Finding information abour connecting to SQLEXPRESS on remote webserver

Hello,
I have a MS Windows 2003 server with SQLEXPRESS 2005 running on it. I can
work with the SQL management studio on that machine in my database but not
with a remote desktop. On that server there's also a website www.abc.com
Where can I find enough info to make a connection from within this website
(an ASP.NET *.aspx) to my xyz.mdf database on that server in SQLEXPRESS. I'm
trying for several weeks now and I can't get this thing running.
Thanks for any help on this !
Benny
I am not sure i grasped the issue correctly but have a look at these Express
related articles, especially the remote access ones.
http://msdn2.microsoft.com/en-us/library/ms345154.aspx Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn2.microsoft.com/en-us/library/bb264564.aspx User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn2.microsoft.com/en-us/library/bb264562.aspx Embedding Express in
Apps
http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en
SSMS Express
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Andrew J. Kelly SQL MVP
"BenCoo" <teddyb58@.hotmail.com> wrote in message
news:UDC0i.168283$vc6.1048670@.phobos.telenet-ops.be...
> Hello,
> I have a MS Windows 2003 server with SQLEXPRESS 2005 running on it. I can
> work with the SQL management studio on that machine in my database but not
> with a remote desktop. On that server there's also a website www.abc.com
> Where can I find enough info to make a connection from within this website
> (an ASP.NET *.aspx) to my xyz.mdf database on that server in SQLEXPRESS.
> I'm trying for several weeks now and I can't get this thing running.
> Thanks for any help on this !
> Benny
>
|||Hi,
thanks a lot for your information, it's helped me a lot! Now i only get the
error :
Cannot open database "abc" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Greetings,
Benny
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:unQdmywkHHA.3472@.TK2MSFTNGP04.phx.gbl...
>I am not sure i grasped the issue correctly but have a look at these
>Express related articles, especially the remote access ones.
> http://msdn2.microsoft.com/en-us/library/ms345154.aspx Express Overview
> http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
> with MSDE
> http://msdn2.microsoft.com/en-us/library/bb264564.aspx User Instances
> http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
> for Remote Access
> https://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
> Configuring Remote Access
> http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
> remote access
> http://msdn2.microsoft.com/en-us/library/bb264562.aspx Embedding Express
> in Apps
> http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en
> SSMS Express
> http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
> Backing up Express
> --
> Andrew J. Kelly SQL MVP
> "BenCoo" <teddyb58@.hotmail.com> wrote in message
> news:UDC0i.168283$vc6.1048670@.phobos.telenet-ops.be...
>
|||I am not an Express expert so hopefully someone else will jump in if I am
wrong. But it sounds like you are connecting with Windows Authentication and
that account is not setup as a login or does not have the permissions to
that db. Login with sa or an sa account with the Express Management utility
and ensure that account is setup as a proper login and has rights to that
db.
Andrew J. Kelly SQL MVP
"BenCoo" <teddyb58@.hotmail.com> wrote in message
news:T7I0i.168773$rm4.1182860@.phobos.telenet-ops.be...
> Hi,
> thanks a lot for your information, it's helped me a lot! Now i only get
> the error :
> Cannot open database "abc" requested by the login. The login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Greetings,
> Benny
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
> news:unQdmywkHHA.3472@.TK2MSFTNGP04.phx.gbl...
>
|||Hello Andrew,
"sa" or an "sa account" I don't understand...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:u2GnyozkHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I am not an Express expert so hopefully someone else will jump in if I am
>wrong. But it sounds like you are connecting with Windows Authentication
>and that account is not setup as a login or does not have the permissions
>to that db. Login with sa or an sa account with the Express Management
>utility and ensure that account is setup as a proper login and has rights
>to that db.
> --
> Andrew J. Kelly SQL MVP
> "BenCoo" <teddyb58@.hotmail.com> wrote in message
> news:T7I0i.168773$rm4.1182860@.phobos.telenet-ops.be...
>
|||sa is the SQL Login and system admin role for SQL Server. You can have other
logins in the sa role as well. I am suggesting you use one of those Logins
that are in the sa role (if not sa itself) to log into SQL Server and check
to ensure that windows account has proper access.
Andrew J. Kelly SQL MVP
"BenCoo" <teddyb58@.hotmail.com> wrote in message
news:Gkj1i.172312$Kn2.1218667@.phobos.telenet-ops.be...
> Hello Andrew,
> "sa" or an "sa account" I don't understand...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
> news:u2GnyozkHHA.4772@.TK2MSFTNGP05.phx.gbl...
>

Finding information abour connecting to SQLEXPRESS on remote webserver

Hello,
I have a MS Windows 2003 server with SQLEXPRESS 2005 running on it. I can
work with the SQL management studio on that machine in my database but not
with a remote desktop. On that server there's also a website www.abc.com
Where can I find enough info to make a connection from within this website
(an ASP.NET *.aspx) to my xyz.mdf database on that server in SQLEXPRESS. I'm
trying for several weeks now and I can't get this thing running.
Thanks for any help on this !
BennyI am not sure i grasped the issue correctly but have a look at these Express
related articles, especially the remote access ones.
http://msdn2.microsoft.com/en-us/library/ms345154.aspx Express Overview
http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
with MSDE
http://msdn2.microsoft.com/en-us/library/bb264564.aspx User Instances
http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
for Remote Access
https://blogs.msdn.com/sqlexpress/a.../05/415084.aspx
Configuring Remote Access
http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
remote access
http://msdn2.microsoft.com/en-us/library/bb264562.aspx Embedding Express in
Apps
http://www.microsoft.com/downloads/...&DisplayLang=en
SSMS Express
http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
Backing up Express
Andrew J. Kelly SQL MVP
"BenCoo" <teddyb58@.hotmail.com> wrote in message
news:UDC0i.168283$vc6.1048670@.phobos.telenet-ops.be...
> Hello,
> I have a MS Windows 2003 server with SQLEXPRESS 2005 running on it. I can
> work with the SQL management studio on that machine in my database but not
> with a remote desktop. On that server there's also a website www.abc.com
> Where can I find enough info to make a connection from within this website
> (an ASP.NET *.aspx) to my xyz.mdf database on that server in SQLEXPRESS.
> I'm trying for several weeks now and I can't get this thing running.
> Thanks for any help on this !
> Benny
>|||Hi,
thanks a lot for your information, it's helped me a lot! Now i only get the
error :
Cannot open database "abc" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
Greetings,
Benny
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:unQdmywkHHA.3472@.TK2MSFTNGP04.phx.gbl...
>I am not sure i grasped the issue correctly but have a look at these
>Express related articles, especially the remote access ones.
> http://msdn2.microsoft.com/en-us/library/ms345154.aspx Express Overview
> http://msdn2.microsoft.com/en-us/library/ms165672.aspx Comparing Express
> with MSDE
> http://msdn2.microsoft.com/en-us/library/bb264564.aspx User Instances
> http://www.datamasker.com/SSE2005_NetworkCfg.htm Configuring Express
> for Remote Access
> https://blogs.msdn.com/sqlexpress/a.../05/415084.aspx
> Configuring Remote Access
> http://www.kbalertz.com/Feedback_914277.aspx Configuring SQL2005 for
> remote access
> http://msdn2.microsoft.com/en-us/library/bb264562.aspx Embedding Express
> in Apps
> http://www.microsoft.com/downloads/...&DisplayLang=en
> SSMS Express
> http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
> Backing up Express
> --
> Andrew J. Kelly SQL MVP
> "BenCoo" <teddyb58@.hotmail.com> wrote in message
> news:UDC0i.168283$vc6.1048670@.phobos.telenet-ops.be...
>|||I am not an Express expert so hopefully someone else will jump in if I am
wrong. But it sounds like you are connecting with Windows Authentication and
that account is not setup as a login or does not have the permissions to
that db. Login with sa or an sa account with the Express Management utility
and ensure that account is setup as a proper login and has rights to that
db.
Andrew J. Kelly SQL MVP
"BenCoo" <teddyb58@.hotmail.com> wrote in message
news:T7I0i.168773$rm4.1182860@.phobos.telenet-ops.be...
> Hi,
> thanks a lot for your information, it's helped me a lot! Now i only get
> the error :
> Cannot open database "abc" requested by the login. The login failed.
> Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
> Greetings,
> Benny
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
> news:unQdmywkHHA.3472@.TK2MSFTNGP04.phx.gbl...
>|||Hello Andrew,
"sa" or an "sa account" I don't understand...
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
news:u2GnyozkHHA.4772@.TK2MSFTNGP05.phx.gbl...
>I am not an Express expert so hopefully someone else will jump in if I am
>wrong. But it sounds like you are connecting with Windows Authentication
>and that account is not setup as a login or does not have the permissions
>to that db. Login with sa or an sa account with the Express Management
>utility and ensure that account is setup as a proper login and has rights
>to that db.
> --
> Andrew J. Kelly SQL MVP
> "BenCoo" <teddyb58@.hotmail.com> wrote in message
> news:T7I0i.168773$rm4.1182860@.phobos.telenet-ops.be...
>|||sa is the SQL Login and system admin role for SQL Server. You can have other
logins in the sa role as well. I am suggesting you use one of those Logins
that are in the sa role (if not sa itself) to log into SQL Server and check
to ensure that windows account has proper access.
Andrew J. Kelly SQL MVP
"BenCoo" <teddyb58@.hotmail.com> wrote in message
news:Gkj1i.172312$Kn2.1218667@.phobos.telenet-ops.be...
> Hello Andrew,
> "sa" or an "sa account" I don't understand...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schreef in bericht
> news:u2GnyozkHHA.4772@.TK2MSFTNGP05.phx.gbl...
>

Finding Errors on 64bit Itanium

I have an SSIS job that is exiting with an error on a 64 bit Itanium SQL 2005 sp1. It has three parallel streams of execution. I've been running my tests using dtexec with verbose output being sent to a file.

2 of the streams execute several truncates and then are followed by 4 steps that load data from a SQL 2000 server to a SQL 2005 server. One of the job streams sources it's data using a SQL Server username/password while the other sources it's data using integrated security. Both streams use integrated security for the destinations.

The third job truncates 2 tables and then loads those two tables from an Oracle database. The oracle connections use a username/password in a connection string in a configuration database while the SQL side of the connection uses integrated security.

I've validated security is working, I've validated the drivers for Oracle are working in 64 bit mode and 32 bit mode for the connection strings I'm using, and security for the package. To top it off, this package runs to completion in 32bit mode under the same credentials.

The only warning messages I get running in verbose mode are the following warnings from the Oracle connection:

OnWarning,311-SQL-01,CHARLOTTE\!lcharlton,Import Employee Locator,{EE2C1DB7-A767-4AE6-81F4-6FAE6BFBD2CF},{DF323107-1ABD-4790-A318-B046862C6F58},10/19/2006 2:24:55 PM,10/19/2006 2:24:55 PM,-2145378202,0x,Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Any insights into how to troubleshoot this or possible additional steps I could take are warmly welcomed.

LarryC

The warning you see is due to the OLE DB adapter not being able to retrieve code page information from Oracle. It's a warning and does not usually terminate execution. What error message do you see when execution terminates? How did you validate that the 64-bit Oracle provider is working correctly? Did you do this by executing your package in 64-bit?

Bob

|||

There are no error messages. I've both turned on every event type and every column of information and run the dtexec in verbose mode.

To validate the connection would work in 64 bit I created a VB script using the same connection string and had it display a value from a table. While the script was running I checked the processes to validate whether that was running in *32 mode or native mode.

sql

Finding dashes/hyphens in a column.

Hi,

I'm having trouble running the following query:

select * from message where text_body like ' ----%'

ie, five spaces followed by at least ten hyphens. The query doesn't
complete, so eventually I cancel it. If I remove the hyphens from the
query ("... like ' %'") then it runs fine (though it doesn't find
the correct data).

Am I confusing SQL Server by using a wildcard or regular expression?
(I'm using SQL Server 2000 - 8.99.760).
Thanks in advance for any help

RichardOn 19 Jan 2005 07:46:49 -0800, richard_thomas@.bigfoot.com wrote:

>I'm having trouble running the following query:
>select * from message where text_body like ' ----%'
>ie, five spaces followed by at least ten hyphens. The query doesn't
>complete, so eventually I cancel it. If I remove the hyphens from the
>query ("... like ' %'") then it runs fine (though it doesn't find
>the correct data).
>Am I confusing SQL Server by using a wildcard or regular expression?
>(I'm using SQL Server 2000 - 8.99.760).
>Thanks in advance for any help

Hi Richard,

I couldn't reproduce this behaviour. The following script returns exactly
the one row I expect to be returned:

create table message (text_body varchar(50))
go
insert message values (' ---- should be returned')
insert message values (' --- one dash short')
insert message values (' ---- one space short')
insert message values (' ---- one space too much')
go
select * from message where text_body like ' ----%'
go
drop table message
go

Can you post a script that will reproduce the buggy behaviour on my
machine?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(richard_thomas@.bigfoot.com) writes:
> I'm having trouble running the following query:
> select * from message where text_body like ' ----%'
> ie, five spaces followed by at least ten hyphens. The query doesn't
> complete, so eventually I cancel it. If I remove the hyphens from the
> query ("... like ' %'") then it runs fine (though it doesn't find
> the correct data).

How many rows are there in message? What query plans do you get in
the two cases?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hmm.

I'm beginning to have a feeling that the 'spaces' aren't in fact
spaces.

By doing this, it seems to work:

select * from message where text_body like '____---%'

but also finds strings such as

'----'
ie the first 4 characters are hyphens rather than dashes.

Is there a wildcard that matches whitespace?
Thanks again for your help!|||Sorry, that should read
"The first 4 characters are hyphens rather than spaces" (not dashes!)|||On 20 Jan 2005 02:21:21 -0800, richard_thomas@.bigfoot.com wrote:

>Is there a wildcard that matches whitespace?

Hi Richard,

Unfortunately, no.

You should locate rows that you suspect to have non-space whitespace,
using (e.g.)
WHEREtext_body like '____---%'
ANDtext_body NOT like ' ---%'

Then, inspect the real contents of text_body in such rows:
SELECT ASCII (SUBSTRING (text_body, 1, 1)),
ASCII (SUBSTRING (text_body, 2, 1)),
ASCII (SUBSTRING (text_body, 3, 1)),
ASCII (SUBSTRING (text_body, 4, 1))
FROM ...
WHERE ...

Once you know that, you can start thinking how to match all variations you
may have in your data.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Find SQL Servers

Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.Please take a look here:
http://sqlserver2000.databases.aspf...my-network.html
Cheers,
Paul Ibison|||Thanks a lot
"Paul Ibison" wrote:

> Please take a look here:
> http://sqlserver2000.databases.aspf...my-network.html
> Cheers,
> Paul Ibison
>
>

Find SQL Servers

Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.
Thanks a lot
"Paul Ibison" wrote:

> Please take a look here:
> http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
> Cheers,
> Paul Ibison
>
>

Find SQL Servers

Hi,
Is there a script or some tool that I can use to search for all the SQL
Servers running in my domain? Thanks.Please take a look here:
http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
Cheers,
Paul Ibison|||Thanks a lot
"Paul Ibison" wrote:
> Please take a look here:
> http://sqlserver2000.databases.aspfaq.com/how-do-i-find-all-the-available-sql-servers-on-my-network.html
> Cheers,
> Paul Ibison
>
>

Find SQL Instances on Network

Basic question. I'm new to my position. Lots of SQL on the network but no
documentation. How can I identify where SQL server is running and what
version is there?
Found this article on the net
http://www.databasejournal.com/featu...le.php/3397981 that is
great but only addresses one WAN Segment. HAve about 30 segements to review
and was hoping for one solution to identify all the instances at one time.
Thanks
Jody Howard
"JHoward" <jodyhow(removeme)@.hotmail.com> wrote in message
news:156905AB-BF17-4F62-8A55-1F2443AA4C6D@.microsoft.com...
> Basic question. I'm new to my position. Lots of SQL on the network but
no
> documentation. How can I identify where SQL server is running and what
> version is there?
> Found this article on the net
> http://www.databasejournal.com/featu...le.php/3397981 that is
> great but only addresses one WAN Segment. HAve about 30 segements to
review
> and was hoping for one solution to identify all the instances at one time.
Covered this on another post...
Steve

Find servers runnign reporting services

How can I enumerate only those servers running reporting services in
c#.
I can get a list of all SQL Server instances on the local network
using:
SmoApplication.EnumAvailableSqlServers(false)
How can i filter this list to include only those instances where
Reporting Services is running?
Thanks in advance
Dave ArkleyOn Sep 14, 7:06 am, daveark...@.wildair.net wrote:
> How can I enumerate only those servers running reporting services in
> c#.
> I can get a list of all SQL Server instances on the local network
> using:
> SmoApplication.EnumAvailableSqlServers(false)
> How can i filter this list to include only those instances where
> Reporting Services is running?
> Thanks in advance
> Dave Arkley
You can loop through the list and run a query on each SQL Server
Instance similar to the following to determine if SSRS is installed
(at least for the most part):
SELECT COUNT(*) FROM SYS.DATABASES WHERE [NAME] IN
('REPORTSERVER','REPORTSERVERTEMPDB');
If the query comes back w/a result of 2, then Reporting Services is
installed. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

find query plan

We are using SQL 2005.
What is the query that uses DMVs to track a query plan of a particular sproc
running on a server?
we noticed a particular sproc thats performing poorly when being called from
the client vs when being called from QA and hence wanted to get the query
plan that was called from the client
I know you can get that in 2005 which was not possible in 2000.
What is the query ? I think I have seen it presented and it seems abit
complex that calls plan handles and has a few joins..
ThanksHassan
Try this one ( I could not remember who wrote this function)
CREATE FUNCTION statement_level_query_plan(
@.handle as varbinary(64) -- Handle for the overall query plan
)
RETURNS TABLE as
RETURN (
select
statement_nbr, -- Sequential number of statement
within batch or SP
statement_type, -- SELECT, INSERT, UPDATE, etc
statement_subtree_cost, -- Estimated Query Cost
statement_estimated_rows, -- Estimated Rows Returned
statement_optimization_level, -- FULL or TRIVIAL
statement_text, -- Text of query
statement_plan -- XML Plan To view as a graphical
plan
-- save the column
output to a file with extension .SQLPlan
-- then reopen the
file by double-clicking
from (
select
C.value('@.StatementId','int') as statement_nbr,
C.value('(./@.StatementText)','nvarchar(max)') as
statement_text,
C.value('(./@.StatementType)','varchar(20)') as
statement_type,
C.value('(./@.StatementSubTreeCost)','float') as
statement_subtree_cost,
C.value('(./@.StatementEstRows)','float') as
statement_estimated_rows,
C.value('(./@.StatementOptmLevel)','varchar(20)') as
statement_optimization_level,
-- Construct the XML headers around the single plan that will
permit
-- this column to be used as a graphical showplan.
-- Only generate plan columns where statement has an associated
plan
C.query('declare namespace
PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
if (./PLN:QueryPlan or ./PLN:Condition/PLN:QueryPlan)
then
<PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
{ ./attribute::* }
{ ./descendant::PLN:QueryPlan[1] }
</PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
else ()
') as statement_plan
from
sys.dm_exec_query_plan(@.handle)
CROSS APPLY
-- This expression finds all nodes containing attribute
StatementText
-- regardless of how deep they are in the potentially nested
batch hierarchy
-- The results of this expression are processed by the Select
expressions above
query_plan.nodes('declare namespace
PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
as T(C)
) x
)
--So, the query to report all executing
--SQL statements and their plans is:
select pln.*, req.* from sys.dm_exec_requests as req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from
master.sys.dm_exec_sql_text(sql_handle)),
statement_start_offset/2,
1+ case when statement_end_offset = -1
then LEN((select text from
master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
else statement_end_offset/2 -
statement_start_offset/2
end)
,3000)
, '[','[[]') + '%'
--Even more useful,
--the function can be joined to the
--view sys.dm_exec_query_stats that reports
-- per-statement IO, CPU and duration statistics.
-- For example, to find the plans for the
--10 queries with longest average durations:
select pln.*, req.* from
(select top 10 * from sys.dm_exec_query_stats
order by total_elapsed_time/execution_count desc ) req
CROSS APPLY statement_level_query_plan(plan_handle) as pln
where statement_text like
'%' +
replace(
left(
substring((select text from
master.sys.dm_exec_sql_text(sql_handle)),
statement_start_offset/2,
1+ case when statement_end_offset = -1
then LEN((select text from
master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
else statement_end_offset/2 -
statement_start_offset/2
end)
,3000)
, '[','[[]') + '%'
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
> We are using SQL 2005.
> What is the query that uses DMVs to track a query plan of a particular
> sproc running on a server?
> we noticed a particular sproc thats performing poorly when being called
> from the client vs when being called from QA and hence wanted to get the
> query plan that was called from the client
> I know you can get that in 2005 which was not possible in 2000.
> What is the query ? I think I have seen it presented and it seems abit
> complex that calls plan handles and has a few joins..
> Thanks
>|||Uri,
Could you attach the file ? The entire formatting is not coming along well
and hence nor parsing.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e5uuDDUFIHA.1056@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Try this one ( I could not remember who wrote this function)
> CREATE FUNCTION statement_level_query_plan(
> @.handle as varbinary(64) -- Handle for the overall query plan
> )
> RETURNS TABLE as
> RETURN (
> select
> statement_nbr, -- Sequential number of statement
> within batch or SP
> statement_type, -- SELECT, INSERT, UPDATE,
> etc
> statement_subtree_cost, -- Estimated Query Cost
> statement_estimated_rows, -- Estimated Rows Returned
> statement_optimization_level, -- FULL or TRIVIAL
> statement_text, -- Text of query
> statement_plan -- XML Plan To view as a graphical
> plan
> -- save the
> column output to a file with extension .SQLPlan
> -- then reopen
> the file by double-clicking
> from (
> select
> C.value('@.StatementId','int') as statement_nbr,
> C.value('(./@.StatementText)','nvarchar(max)') as
> statement_text,
> C.value('(./@.StatementType)','varchar(20)') as
> statement_type,
> C.value('(./@.StatementSubTreeCost)','float') as
> statement_subtree_cost,
> C.value('(./@.StatementEstRows)','float') as
> statement_estimated_rows,
> C.value('(./@.StatementOptmLevel)','varchar(20)') as
> statement_optimization_level,
> -- Construct the XML headers around the single plan that will
> permit
> -- this column to be used as a graphical showplan.
> -- Only generate plan columns where statement has an
> associated plan
> C.query('declare namespace
> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> if (./PLN:QueryPlan or
> ./PLN:Condition/PLN:QueryPlan)
> then
>
> <PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
> { ./attribute::* }
> { ./descendant::PLN:QueryPlan[1] }
>
> </PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
> else ()
> ') as statement_plan
> from
> sys.dm_exec_query_plan(@.handle)
> CROSS APPLY
> -- This expression finds all nodes containing attribute
> StatementText
> -- regardless of how deep they are in the potentially nested
> batch hierarchy
> -- The results of this expression are processed by the Select
> expressions above
> query_plan.nodes('declare namespace
> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>
> /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
> as T(C)
> ) x
> )
>
> --So, the query to report all executing
> --SQL statements and their plans is:
> select pln.*, req.* from sys.dm_exec_requests as req
> CROSS APPLY statement_level_query_plan(plan_handle) as pln
> where statement_text like
> '%' +
> replace(
> left(
> substring((select text from
> master.sys.dm_exec_sql_text(sql_handle)),
> statement_start_offset/2,
> 1+ case when statement_end_offset = -1
> then LEN((select text from
> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
> else statement_end_offset/2 -
> statement_start_offset/2
> end)
> ,3000)
> , '[','[[]') + '%'
>
> --Even more useful,
> --the function can be joined to the
> --view sys.dm_exec_query_stats that reports
> -- per-statement IO, CPU and duration statistics.
> -- For example, to find the plans for the
> --10 queries with longest average durations:
>
> select pln.*, req.* from
> (select top 10 * from sys.dm_exec_query_stats
> order by total_elapsed_time/execution_count desc ) req
> CROSS APPLY statement_level_query_plan(plan_handle) as pln
> where statement_text like
> '%' +
> replace(
> left(
> substring((select text from
> master.sys.dm_exec_sql_text(sql_handle)),
> statement_start_offset/2,
> 1+ case when statement_end_offset = -1
> then LEN((select text from
> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
> else statement_end_offset/2 -
> statement_start_offset/2
> end)
> ,3000)
> , '[','[[]') + '%'
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> We are using SQL 2005.
>> What is the query that uses DMVs to track a query plan of a particular
>> sproc running on a server?
>> we noticed a particular sproc thats performing poorly when being called
>> from the client vs when being called from QA and hence wanted to get the
>> query plan that was called from the client
>> I know you can get that in 2005 which was not possible in 2000.
>> What is the query ? I think I have seen it presented and it seems abit
>> complex that calls plan handles and has a few joins..
>> Thanks
>|||Sure
"Hassan" <hassan@.hotmail.com> wrote in message
news:%239yIidZFIHA.2004@.TK2MSFTNGP06.phx.gbl...
> Uri,
> Could you attach the file ? The entire formatting is not coming along well
> and hence nor parsing.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e5uuDDUFIHA.1056@.TK2MSFTNGP03.phx.gbl...
>> Hassan
>> Try this one ( I could not remember who wrote this function)
>> CREATE FUNCTION statement_level_query_plan(
>> @.handle as varbinary(64) -- Handle for the overall query plan
>> )
>> RETURNS TABLE as
>> RETURN (
>> select
>> statement_nbr, -- Sequential number of statement
>> within batch or SP
>> statement_type, -- SELECT, INSERT, UPDATE,
>> etc
>> statement_subtree_cost, -- Estimated Query Cost
>> statement_estimated_rows, -- Estimated Rows Returned
>> statement_optimization_level, -- FULL or TRIVIAL
>> statement_text, -- Text of query
>> statement_plan -- XML Plan To view as a
>> graphical
>> plan
>> -- save the
>> column output to a file with extension .SQLPlan
>> -- then reopen
>> the file by double-clicking
>> from (
>> select
>> C.value('@.StatementId','int') as statement_nbr,
>> C.value('(./@.StatementText)','nvarchar(max)') as
>> statement_text,
>> C.value('(./@.StatementType)','varchar(20)') as
>> statement_type,
>> C.value('(./@.StatementSubTreeCost)','float') as
>> statement_subtree_cost,
>> C.value('(./@.StatementEstRows)','float') as
>> statement_estimated_rows,
>> C.value('(./@.StatementOptmLevel)','varchar(20)') as
>> statement_optimization_level,
>> -- Construct the XML headers around the single plan that will
>> permit
>> -- this column to be used as a graphical showplan.
>> -- Only generate plan columns where statement has an
>> associated plan
>> C.query('declare namespace
>> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>> if (./PLN:QueryPlan or
>> ./PLN:Condition/PLN:QueryPlan)
>> then
>>
>> <PLN:ShowPlanXML><PLN:BatchSequence><PLN:Batch><PLN:Statements><PLN:StmtSimple>
>> { ./attribute::* }
>> { ./descendant::PLN:QueryPlan[1] }
>>
>> </PLN:StmtSimple></PLN:Statements></PLN:Batch></PLN:BatchSequence></PLN:ShowPlanXML>
>> else ()
>> ') as statement_plan
>> from
>> sys.dm_exec_query_plan(@.handle)
>> CROSS APPLY
>> -- This expression finds all nodes containing attribute
>> StatementText
>> -- regardless of how deep they are in the potentially nested
>> batch hierarchy
>> -- The results of this expression are processed by the Select
>> expressions above
>> query_plan.nodes('declare namespace
>> PLN="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
>>
>> /PLN:ShowPlanXML/PLN:BatchSequence/PLN:Batch/PLN:Statements/descendant::*[attribute::StatementText]')
>> as T(C)
>> ) x
>> )
>>
>> --So, the query to report all executing
>> --SQL statements and their plans is:
>> select pln.*, req.* from sys.dm_exec_requests as req
>> CROSS APPLY statement_level_query_plan(plan_handle) as pln
>> where statement_text like
>> '%' +
>> replace(
>> left(
>> substring((select text from
>> master.sys.dm_exec_sql_text(sql_handle)),
>> statement_start_offset/2,
>> 1+ case when statement_end_offset = -1
>> then LEN((select text from
>> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
>> else statement_end_offset/2 -
>> statement_start_offset/2
>> end)
>> ,3000)
>> , '[','[[]') + '%'
>>
>> --Even more useful,
>> --the function can be joined to the
>> --view sys.dm_exec_query_stats that reports
>> -- per-statement IO, CPU and duration statistics.
>> -- For example, to find the plans for the
>> --10 queries with longest average durations:
>>
>> select pln.*, req.* from
>> (select top 10 * from sys.dm_exec_query_stats
>> order by total_elapsed_time/execution_count desc ) req
>> CROSS APPLY statement_level_query_plan(plan_handle) as pln
>> where statement_text like
>> '%' +
>> replace(
>> left(
>> substring((select text from
>> master.sys.dm_exec_sql_text(sql_handle)),
>> statement_start_offset/2,
>> 1+ case when statement_end_offset = -1
>> then LEN((select text from
>> master.sys.dm_exec_sql_text(sql_handle))) - statement_start_offset/2
>> else statement_end_offset/2 -
>> statement_start_offset/2
>> end)
>> ,3000)
>> , '[','[[]') + '%'
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23QcCMTSFIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> We are using SQL 2005.
>> What is the query that uses DMVs to track a query plan of a particular
>> sproc running on a server?
>> we noticed a particular sproc thats performing poorly when being called
>> from the client vs when being called from QA and hence wanted to get the
>> query plan that was called from the client
>> I know you can get that in 2005 which was not possible in 2000.
>> What is the query ? I think I have seen it presented and it seems abit
>> complex that calls plan handles and has a few joins..
>> Thanks
>>
>
begin 666 Query(Problems).txt
M0U)%051%($953D-424].('-T871E;65N=%]L979E;%]Q=65R>5]P;&%N* T*
M#0H@.(" @.(" @.($!H86YD;&4@.87,@.=F%R8FEN87)Y*#8T*2 M+2!(86YD;&4@.
M9F]R('1H92!O=F5R86QL('%U97)Y('!L86X-"@.T**0T*#0I215154DY3(%1!
M0DQ%(&%S( T*#0I215154DX@.* T*#0H@.('-E;&5C=" -"@.T*(" @.(" @.("!S
M=&%T96UE;G1?;F)R+" @.(" @.(" @.(" @.(" @.(" @.+2T@.4V5Q=65N=&EA;"!N
M=6UB97(@.;V8@.<W1A=&5M96YT('=I=&AI;B!B871C:"!O<B!34 T*#0H@.(" @.
M(" @.('-T871E;65N=%]T>7!E+" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.+2T@.
M4T5,14-4+"!)3E-%4E0L(%501$%412P@.971C#0H-"B @.(" @.(" @.<W1A=&5M
M96YT7W-U8G1R965?8V]S="P@.(" @.(" @.(" @.(" @.(" M+2!%<W1I;6%T960@.
M475E<GD@.0V]S= T*#0H@.(" @.(" @.('-T871E;65N=%]E<W1I;6%T961?<F]W
M<RP@.(" @.(" @.(" @.(" @.+2T@.17-T:6UA=&5D(%)O=W,@.4F5T=7)N960-"@.T*
M(" @.(" @.("!S=&%T96UE;G1?;W!T:6UI>F%T:6]N7VQE=F5L+" @.(" @.(" @.
M("TM($953$P@.;W(@.5%))5DE!3 T*#0H@.(" @.(" @.('-T871E;65N=%]T97AT
M+" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.+2T@.5&5X="!O9B!Q=65R>0T*#0H@.
M(" @.(" @.('-T871E;65N=%]P;&%N(" @.(" @.(" @.(" @.(" @.("TM(%A-3"!0
M;&%N(" @.(%1O('9I97<@.87,@.82!G<F%P:&EC86P@.<&QA;@.T*#0H@.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M("TM(" @.(" @.<V%V92!T:&4@.8V]L=6UN(&]U='!U="!T;R!A(&9I;&4@.=VET
M:"!E>'1E;G-I;VX@.+E-13%!L86X-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" M+2 @.(" @.('1H96X@.
M<F5O<&5N('1H92!F:6QE(&)Y(&1O=6)L92UC;&EC:VEN9PT*#0H@.("!F<F]M
M("@.-"@.T*(" @.(" @.("!S96QE8W0@.#0H-"B @.(" @.(" @.(" @.(" @.($,N=F%L
M=64H)T!3=&%T96UE;G1)9"<L)VEN="<I(&%S('-T871E;65N=%]N8G(L#0H-
M"B @.(" @.(" @.(" @.(" @.($,N=F%L=64H)R@.N+T!3=&%T96UE;G1497AT*2<L
M)VYV87)C:&%R*&UA>"DG*2!A<R!S=&%T96UE;G1?=&5X="P-"@.T*(" @.(" @.
M(" @.(" @.(" @.0RYV86QU92@.G*"XO0%-T871E;65N=%1Y<&4I)RPG=F%R8VAA
M<B@.R,"DG*2!A<R!S=&%T96UE;G1?='EP92P-"@.T*(" @.(" @.(" @.(" @.(" @.
M0RYV86QU92@.G*"XO0%-T871E;65N=%-U8E1R965#;W-T*2<L)V9L;V%T)RD@.
M87,@.<W1A=&5M96YT7W-U8G1R965?8V]S="P-"@.T*(" @.(" @.(" @.(" @.(" @.
M0RYV86QU92@.G*"XO0%-T871E;65N=$5S=%)O=W,I)RPG9FQO870G*2!A<R!S
M=&%T96UE;G1?97-T:6UA=&5D7W)O=W,L#0H-"B @.(" @.(" @.(" @.(" @.($,N
M=F%L=64H)R@.N+T!3=&%T96UE;G1/<'1M3&5V96PI)RPG=F%R8VAA<B@.R,"DG
M*2!A<R!S=&%T96UE;G1?;W!T:6UI>F%T:6]N7VQE=F5L+ T*#0HM+2 @.(" @.
M(" @.(" @.("!#;VYS=')U8W0@.=&AE(%A-3"!H96%D97)S(&%R;W5N9"!T:&4@.
M<VEN9VQE('!L86X@.=&AA="!W:6QL('!E<FUI= T*#0HM+2 @.(" @.(" @.(" @.
M("!T:&ES(&-O;'5M;B!T;R!B92!U<V5D(&%S(&$@.9W)A<&AI8V%L('-H;W=P
M;&%N+@.T*#0HM+2 @.(" @.(" @.(" @.("!/;FQY(&=E;F5R871E('!L86X@.8V]L
M=6UN<R!W:&5R92!S=&%T96UE;G0@.:&%S(&%N(&%S<V]C:6%T960@.<&QA;@.T*
M#0H@.(" @.(" @.(" @.(" @.("!#+G%U97)Y*"=D96-L87)E(&YA;65S<&%C92!0
M3$X](FAT=' Z+R]S8VAE;6%S+FUI8W)O<V]F="YC;VTO<W%L<V5R=F5R+S(P
M,#0O,#<O<VAO=W!L86XB.PT*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(&EF
M("@.N+U!,3CI1=65R>5!L86X@.;W(@.+B]03$XZ0V]N9&ET:6]N+U!,3CI1=65R
M>5!L86XI( T*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.('1H96X-"@.T*(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#Q03$XZ4VAO=U!L86Y834P^
M/%!,3CI"871C:%-E<75E;F-E/CQ03$XZ0F%T8V@.^/%!,3CI3=&%T96UE;G1S
M/CQ03$XZ4W1M=%-I;7!L93X-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.>R N+V%T=')I8G5T93HZ*B!]#0H-"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.('L@.+B]D97-C96YD86YT.CI03$XZ475E<GE0;&%N6S%=M('T-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#PO4$Q..E-T
M;713:6UP;&4^/"]03$XZ4W1A=&5M96YT<SX\+U!,3CI"871C:#X\+U!,3CI"
M871C:%-E<75E;F-E/CPO4$Q..E-H;W=0;&%N6$U,/@.T*#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(&5L<V4@.*"D-"@.T*(" @.(" @.(" @.(" @.(" @.)RD@.87,@.
M<W1A=&5M96YT7W!L86X-"@.T*(" @.(" @.("!F<F]M( T*#0H@.(" @.(" @.(" @.
M(" @.("!S>7,N9&U?97AE8U]Q=65R>5]P;&%N*$!H86YD;&4I#0H-"B @.(" @.
M(" @.0U)/4U,@.05!03%D@.#0H-"BTM(" @.(" @.(" @.(" @.(%1H:7,@.97AP<F5S
M<VEO;B!F:6YD<R!A;&P@.;F]D97,@.8V]N=&%I;FEN9R!A='1R:6)U=&4@.4W1A
M=&5M96YT5&5X= T*#0HM+2 @.(" @.(" @.(" @.("!R96=A<F1L97-S(&]F(&AO
M=R!D965P('1H97D@.87)E(&EN('1H92!P;W1E;G1I86QL>2!N97-T960@.8F%T
M8V@.@.:&EE<F%R8VAY#0H-"BTM(" @.(" @.(" @.(" @.(%1H92!R97-U;'1S(&]F
M('1H:7,@.97AP<F5S<VEO;B!A<F4@.<')O8V5S<V5D(&)Y('1H92!396QE8W0@.
M97AP<F5S<VEO;G,@.86)O=F4-"@.T*(" @.(" @.(" @.(" @.(" @.<75E<GE?<&QA
M;BYN;V1E<R@.G9&5C;&%R92!N86UE<W!A8V4@.4$Q./2)H='1P.B\O<V-H96UA
M<RYM:6-R;W-O9G0N8V]M+W-Q;'-E<G9E<B\R,# T+S W+W-H;W=P;&%N(CL-
M"@.T*(" @.(" @.(" @.(" @.(" @.("]03$XZ4VAO=U!L86Y834PO4$Q..D)A=&-H
M4V5Q=65N8V4O4$Q..D)A=&-H+U!,3CI3=&%T96UE;G1S+V1E<V-E;F1A;G0Z
M.BI;871T<FEB=71E.CI3=&%T96UE;G1497AT72<I( T*#0H@.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(&%S(%0H0RD@.#0H-"B @.(" @.(" @.*2!X#0H-"B @.*0T*
M#0H-"B M+5-O+"!T:&4@.<75E<GD@.=&\@.<F5P;W)T(&%L;"!E>&5C=71I;F<@.
M#0HM+5-13"!S=&%T96UE;G1S(&%N9"!T:&5I<B!P;&%N<R!I<SH@.#0H-"G-E
M;&5C="!P;&XN*BP@.<F5Q+BH@.9G)O;2!S>7,N9&U?97AE8U]R97%U97-T<R!A
M<R!R97$-"@.T*0U)/4U,@.05!03%D@.<W1A=&5M96YT7VQE=F5L7W%U97)Y7W!L
M86XH<&QA;E]H86YD;&4I(&%S('!L;@.T*#0IW:&5R92!S=&%T96UE;G1?=&5X
M="!L:6ME#0H-"B<E)R K#0H-"G)E<&QA8V4H#0H-"FQE9G0H#0H-"B @.(" @.
M(" @.(" @.(" @.('-U8G-T<FEN9R@.H<V5L96-T('1E>'0@.9G)O;2!M87-T97(N
M<WES+F1M7V5X96-?<W%L7W1E>'0H<W%L7VAA;F1L92DI+" -"@.T*(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.("!S=&%T96UE;G1?<W1A<G1?;V9F<V5T+S(L( T*
M#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(#$K(" @.(" @.8V%S92!W:&5N('-T
M871E;65N=%]E;F1?;V9F<V5T(#T@.+3$@.#0H-"B @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.('1H96X@.3$5.*"AS96QE8W0@.=&5X="!F<F]M(&UA<W1E
M<BYS>7,N9&U?97AE8U]S<6Q?=&5X="AS<6Q?:&%N9&QE*2DI("T@.<W1A=&5M
M96YT7W-T87)T7V]F9G-E="\R#0H-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.("!E;'-E('-T871E;65N=%]E;F1?;V9F<V5T+S(@.+2!S=&%T96UE
M;G1?<W1A<G1?;V9F<V5T+S(@.#0H-"B @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(&5N9"D@.#0H-"B @.(" @.(" @.+#,P,# I#0H-"BP@.)ULG+"=;6UTG
M*2 K("<E)PT*#0H-"BTM179E;B!M;W)E('5S969U;"P@.#0HM+71H92!F=6YC
M=&EO;B!C86X@.8F4@.:F]I;F5D('1O('1H92 -"BTM=FEE=R!S>7,N9&U?97AE
M8U]Q=65R>5]S=&%T<R!T:&%T(')E<&]R=',-"BTM('!E<BUS=&%T96UE;G0@.
M24\L($-052!A;F0@.9'5R871I;VX@.<W1A=&ES=&EC<RX@.( T*+2T@.1F]R(&5X
M86UP;&4L('1O(&9I;F0@.=&AE('!L86YS(&9O<B!T:&4@.#0HM+3$P('%U97)I
M97,@.=VET:"!L;VYG97-T(&%V97)A9V4@.9'5R871I;VYS.@.T*#0H@.#0H-"G-E
M;&5C="!P;&XN*BP@.<F5Q+BH@.9G)O;0T*#0HH<V5L96-T('1O<" Q," J(&9R
M;VT@.<WES+F1M7V5X96-?<75E<GE?<W1A=',@.#0H-"F]R9&5R(&)Y('1O=&%L
M7V5L87!S961?=&EM92]E>&5C=71I;VY?8V]U;G0@.9&5S8R I(')E<0T*#0I#
M4D]34R!!4%!,62!S=&%T96UE;G1?;&5V96Q?<75E<GE?<&QA;BAP;&%N7VAA
M;F1L92D@.87,@.<&QN#0H-"G=H97)E('-T871E;65N=%]T97AT(&QI:V4-"@.T*
M)R4G("L-"@.T*<F5P;&%C92@.-"@.T*;&5F="@.-"@.T*(" @.(" @.(" @.(" @.(" @.
M<W5B<W1R:6YG*"AS96QE8W0@.=&5X="!F<F]M(&UA<W1E<BYS>7,N9&U?97AE
M8U]S<6Q?=&5X="AS<6Q?:&%N9&QE*2DL( T*#0H@.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.('-T871E;65N=%]S=&%R=%]O9F9S970O,BP@.#0H-"B @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.,2L@.(" @.("!C87-E('=H96X@.<W1A=&5M96YT7V5N
M9%]O9F9S970@./2 M,2 -"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.=&AE;B!,14XH*'-E;&5C="!T97AT(&9R;VT@.;6%S=&5R+G-Y<RYD;5]E
M>&5C7W-Q;%]T97AT*'-Q;%]H86YD;&4I*2D@.+2!S=&%T96UE;G1?<W1A<G1?
M;V9F<V5T+S(-"@.T*(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.96QS
M92!S=&%T96UE;G1?96YD7V]F9G-E="\R("T@.<W1A=&5M96YT7W-T87)T7V]F
M9G-E="\R( T*#0H@.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.("!E;F0I
M( T*#0H@.(" @.(" @.("PS,# P*0T*#0HL("=;)RPG6UM=)RD@.*R G)2<-"@.T*
`
end

Find out whether SQL Server services are runing or not - SP4

Is that possible by querying the SQL Server , to find whether the listed

below there services are running or not?

1. SQL Server

2. SQL Agent

3. MSDTC

No. It is best to do this outside of the database. You can use NT command-line utilities or WMI to do this easily.|||So basically you can run some command-line tool in xp_cmdshell and parse its output, but that is rather lame. :)|||

Let me give some ideas on how WMI ot NT Utilities can be used.

For NT Utilities command are referring command "sc".

Basically I have 100 boxes running with SQL Servers. I want myself to be get alerted when any of the SQL Server instances services stopped.

As all these boxes are production, I am not supposed to create / change anything on these boxes. I have to query those boxes remotly and find out whether SQL Server services are running or not.

Please note there would be some named instances too.

Find out whether SQL Server services are runing or not

Is that possible by querying the SQL Server , to find whether the listed

below there services are running or not?

1. SQL Server

2. SQL Agent

3. MSDTC

No. It is best to do this outside of the database. You can use NT command-line utilities or WMI to do this easily.|||So basically you can run some command-line tool in xp_cmdshell and parse its output, but that is rather lame. :)|||

Let me give some ideas on how WMI ot NT Utilities can be used.

For NT Utilities command are referring command "sc".

Basically I have 100 boxes running with SQL Servers. I want myself to be get alerted when any of the SQL Server instances services stopped.

As all these boxes are production, I am not supposed to create / change anything on these boxes. I have to query those boxes remotly and find out whether SQL Server services are running or not.

Please note there would be some named instances too.

Find out what is written into transaction log

Hi,
My transaction log grows around 5 Gb an hour. I'm trying to find out what is
being written there and running SQL profiler. I can find very few
transactions with Writes other then 0. How can I find out what is being
written into transaction log in such huge volumes?
Have a look at getting a log viewer tool eg Lumigent's LogExplorer or
Redgate's LogRescue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Find out what is written into transaction log

Hi,
My transaction log grows around 5 Gb an hour. I'm trying to find out what is
being written there and running SQL profiler. I can find very few
transactions with Writes other then 0. How can I find out what is being
written into transaction log in such huge volumes?Have a look at getting a log viewer tool eg Lumigent's LogExplorer or
Redgate's LogRescue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Find out what is written into transaction log

Hi,
My transaction log grows around 5 Gb an hour. I'm trying to find out what is
being written there and running SQL profiler. I can find very few
transactions with Writes other then 0. How can I find out what is being
written into transaction log in such huge volumes?Have a look at getting a log viewer tool eg Lumigent's LogExplorer or
Redgate's LogRescue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Find instances of sql running

Is it possible to do the following. If yes then how ?
- Is there a way to find out all the instances of the SQL server running ?
- Read the alerts generated by SQL server.
- Read the logs generated by SQL server.
Thanks,
-FatemaFatema
CREATE PROCEDURE dbo.ListLocalServers
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
END
Read up in the BOL chapter "Viewing the SQL Server Error Log"
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
> Is it possible to do the following. If yes then how ?
> - Is there a way to find out all the instances of the SQL server running ?
> - Read the alerts generated by SQL server.
> - Read the logs generated by SQL server.
> Thanks,
> -Fatema|||Thanks for the reply.
On executing this procedure it gives me an error:
Login Failed. Reason: not associated with a trusted SQL server connection.
What is the problem ?
TIA,
-Fatema
"Uri Dimant" wrote:

> Fatema
> CREATE PROCEDURE dbo.ListLocalServers
> AS
> BEGIN
> SET NOCOUNT ON
> CREATE TABLE #servers(sname VARCHAR(255))
> INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
> DELETE #servers WHERE sname='Servers:'
> SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
> DROP TABLE #servers
> END
>
> Read up in the BOL chapter "Viewing the SQL Server Error Log"
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>|||Fatema
Change (SQL Server Properties)
under Security Tab -Authentication to SQL Server and Windows
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:32B44E26-E20A-40A7-BB3C-CC526F2E0BF3@.microsoft.com...
> Thanks for the reply.
> On executing this procedure it gives me an error:
> Login Failed. Reason: not associated with a trusted SQL server connection.
> What is the problem ?
> TIA,
> -Fatema
> "Uri Dimant" wrote:
>
running ?|||I am getting the following o/p :
Name (column)
--
Password
1>
WHat does this mean ?
-Fatema
"Uri Dimant" wrote:

> Fatema
> Change (SQL Server Properties)
> under Security Tab -Authentication to SQL Server and Windows
>
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:32B44E26-E20A-40A7-BB3C-CC526F2E0BF3@.microsoft.com...
> running ?
>
>|||1. Not in a reliable way. Uri posted a method, but please be aware that all
methods (all are based
on API calls in Windows) are based on browser service in Windows and it is n
ot guaranteed to show
all servers, also servers can be in the browser list after it was shut down.
2. Can you define what you mean by alerts?
3. Can you define what you mean by "logs"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
> Is it possible to do the following. If yes then how ?
> - Is there a way to find out all the instances of the SQL server running ?
> - Read the alerts generated by SQL server.
> - Read the logs generated by SQL server.
> Thanks,
> -Fatema|||Can you please tell me how is it possible to programmatically find out all
instances of sql server that are running ?
By logs I mean the logs that sql server generates during its course of
execution.
Thanks,
-fatema
"Tibor Karaszi" wrote:

> 1. Not in a reliable way. Uri posted a method, but please be aware that al
l methods (all are based
> on API calls in Windows) are based on browser service in Windows and it is
not guaranteed to show
> all servers, also servers can be in the browser list after it was shut dow
n.
> 2. Can you define what you mean by alerts?
> 3. Can you define what you mean by "logs"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>|||> Can you please tell me how is it possible to programmatically find out all
> instances of sql server that are running ?
As I already replied: Not reliably. Search the newsgroup archives and you wi
ll probably find posts
from Gert Drapers specifying the technicalities behind this. If you want to
list the servers anyhow
(unreliably), you have classes for that in, for instance, SQLDMO. See www.sqldev.n
et for examples.

> By logs I mean the logs that sql server generates during its course of
> execution.
Errorlog? Transaction log? Entries the in the eventlog? Do you want to get t
o this using TSQL or a
client programming language, like VB.NET?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:353E84EC-5811-4D21-86A4-19B8F5C3A69E@.microsoft.com...
> Can you please tell me how is it possible to programmatically find out all
> instances of sql server that are running ?
> By logs I mean the logs that sql server generates during its course of
> execution.
> Thanks,
> -fatema
>
> "Tibor Karaszi" wrote:
>|||Tibor,
Another problem with OSQL -L is that it list whatever alias you create with
"Client Network Utility" even though there is not a physical server
associated to this alias.
AMB
"Tibor Karaszi" wrote:

> 1. Not in a reliable way. Uri posted a method, but please be aware that al
l methods (all are based
> on API calls in Windows) are based on browser service in Windows and it is
not guaranteed to show
> all servers, also servers can be in the browser list after it was shut dow
n.
> 2. Can you define what you mean by alerts?
> 3. Can you define what you mean by "logs"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:02D6FC75-FC5B-47A8-ACAE-90D350B4FC90@.microsoft.com...
>
>

Find blocking process

Hi,
we're having a problem with SQL 2000 and Opta 2000 JDBC driver
where there is large update running and at the same time,
read is blocked for a while.
We're looking for a way to catch this blocking process
and if it last more than 10 minutes, then email or send out a message.
I know sp_lock returns all current locks
but how do you know which one is blocking other processes?

Thanks for your help in advance.neo (second714@.hotmail.com) writes:
> we're having a problem with SQL 2000 and Opta 2000 JDBC driver
> where there is large update running and at the same time,
> read is blocked for a while.
> We're looking for a way to catch this blocking process
> and if it last more than 10 minutes, then email or send out a message.
> I know sp_lock returns all current locks
> but how do you know which one is blocking other processes?

The simplest way is to use sp_who. If a process is blocked, you will
see a non-zero value in the Blk column. This is the spid of the blocker.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Find all read-only Databases using Stored Procedure

I am running a Stored Procedure that goes through all the databases and
reindexs them. However when it reaches a database that is "read
only" the program quits with an error. If there an easy way to
determine if the database is Read only and skip it if it is?
Using SQL 2000 / 2005
Thanks
-Matt-
DECLARE @.ExecSQLcmd varchar(1024)
DECLARE @.DBNum_to_Name int
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
100'
FROM master.dbo.sysdatabases
WHERE dbid =@.DBNum_to_Name
EXEC (@.ExecSQLcmd)
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
DBs here.
ENDAND DATABASEPROPERTY(name, 'IsReadOnly') = 0
<MKruer@.gmail.com> wrote in message
news:1138305254.208071.14400@.g14g2000cwa.googlegroups.com...
>I am running a Stored Procedure that goes through all the databases and
> reindexs them. However when it reaches a database that is "read
> only" the program quits with an error. If there an easy way to
> determine if the database is Read only and skip it if it is?
> Using SQL 2000 / 2005
> Thanks
> -Matt-
>
> DECLARE @.ExecSQLcmd varchar(1024)
> DECLARE @.DBNum_to_Name int
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4
> WHILE @.DBNum_to_Name is not null
> BEGIN
> SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30,
> 100'
> FROM master.dbo.sysdatabases
> WHERE dbid =@.DBNum_to_Name
> EXEC (@.ExecSQLcmd)
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > @.DBNum_to_Name and dbid != 9 and dbid != 25 -- Exclude
> DBs here.
> END
>

Find all instances of MS SQL

How can I find all instances of MS SQL running in my organization? My task to is to audit all instances and stop unautorised or unlicensed instances.
Any help will be appreciated.
Thanks,Originally posted by azam7
How can I find all instances of MS SQL running in my organization? My task to is to audit all instances and stop unautorised or unlicensed instances.

Any help will be appreciated.
Thanks,

You can use OSQL with the -L switch to list servers on you network. I don't know off hand if it will list any across subnets, etc. The command is: isql -L

Mike|||From what I remember it will only list sql servers within that domain - even then it may not be complete.|||Thanks a lot guys...isql -l works
:-)