Profiler - details of SP

How can I setup Profiler to track/watch the execution of the lines in a
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
Thanks,
ThomasLL
Hi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL
|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL
sql

Profiler - details of SP

How can I setup Profiler to track/watch the execution of the lines in a
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
--
Thanks,
ThomasLLHi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL

Profiler - details of SP

How can I setup Profiler to track/watch the execution of the lines in a
stored procedure?
It looks like I just see exec sp_DoWork, as TextData.
I want to see the sql statements in the SP and how long they each take.
--
Thanks,
ThomasLLHi,
I am not sure whether this is possible...
But as soon as you have identified the SP thats taking time, why dont
you check the execution plan of that SP instead?
Thomas.LeBlanc@.NoSpam.Com wrote:
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||Hi Thomas
You can trace SP:StmtStarting and SP:StmtCompleted
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> How can I setup Profiler to track/watch the execution of the lines in a
> stored procedure?
> It looks like I just see exec sp_DoWork, as TextData.
> I want to see the sql statements in the SP and how long they each take.
> --
> Thanks,
> ThomasLL|||That gives me the duration of each Insert, Updaet and Select in the SP?
Thanks Karen, you are a blessing to the SQL Server community
--
Thanks,
Thomas
"Kalen Delaney" wrote:
> Hi Thomas
> You can trace SP:StmtStarting and SP:StmtCompleted
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
> > How can I setup Profiler to track/watch the execution of the lines in a
> > stored procedure?
> >
> > It looks like I just see exec sp_DoWork, as TextData.
> >
> > I want to see the sql statements in the SP and how long they each take.
> > --
> > Thanks,
> > ThomasLL
>
>|||SP:StmtCompleted can give you the duration of each statement in the SP.
Thanks for your kind words. Karen thanks you too. ;-)
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:CC7D6F54-4DE7-4DE8-B7CC-7904572FDF90@.microsoft.com...
> That gives me the duration of each Insert, Updaet and Select in the SP?
>
> Thanks Karen, you are a blessing to the SQL Server community
> --
> Thanks,
> Thomas
>
> "Kalen Delaney" wrote:
>> Hi Thomas
>> You can trace SP:StmtStarting and SP:StmtCompleted
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> http://sqlblog.com
>>
>> "Thomas.LeBlanc@.NoSpam.Com"
>> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
>> news:560746F5-F88B-49A5-9A13-EF873EBDD161@.microsoft.com...
>> > How can I setup Profiler to track/watch the execution of the lines in a
>> > stored procedure?
>> >
>> > It looks like I just see exec sp_DoWork, as TextData.
>> >
>> > I want to see the sql statements in the SP and how long they each take.
>> > --
>> > Thanks,
>> > ThomasLL
>>

Profile statements for sp_prepare/sp_execute

I see a lot of sp_execute statements and cannot find the SQL statements
behind it in profiler.. Eg: i see
exec sp_execute 1, 10, 1
and like thousands and thousands of those when i run the trace for a few
secs.
How can i find out whats being run from a SQL perspective ?
ThanksYou double posted this one. See the replies in the other ng.
--
Andrew J. Kelly SQL MVP
"Hassan" <hassan@.hotmail.com> wrote in message
news:%237mauKlxHHA.312@.TK2MSFTNGP04.phx.gbl...
>I see a lot of sp_execute statements and cannot find the SQL statements
>behind it in profiler.. Eg: i see
> exec sp_execute 1, 10, 1
> and like thousands and thousands of those when i run the trace for a few
> secs.
> How can i find out whats being run from a SQL perspective ?
> Thanks
>

Processor count

How do I get the value of Processor Count in a variable
from xp_msver?
DECLARE @.CPUS int
exec xp_msver N'CprocessorCount'
ThanksNot sure if xpmsver would support output parameters for that specific column
(I doubt it), so you could try this:
CREATE TABLE #xpmsver
(
a INT, b VARCHAR(32), c INT, d VARCHAR(255)
)
INSERT #xpmsver
EXEC master..xp_msver N'processorCount'
DECLARE @.CPUS INT
SELECT @.CPUS = CONVERT(INT, d) FROM #xpmsver
DROP TABLE #xpmsver
PRINT @.cpus
"Bubba Ole" <anoah@.canberra.com> wrote in message
news:00b001c38f40$69096c50$a101280a@.phx.gbl...
> How do I get the value of Processor Count in a variable
> from xp_msver?
> DECLARE @.CPUS int
> exec xp_msver N'CprocessorCount'
> Thanks|||Thanks a million!!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Processing OutPut from exec command

Hi,
I am using exec command command to run xp_cmdshell ext SP.
the command line string given to xp_cmdshell is for executing DTS Packages.
i want to process the out put returned by the exec command in my stored proc
Is it possible to capture the out put in addition of return code(0/1),which
is returned by xp_cmdshell, if yes then how?
thanks in advance
Kailuxdeclare @.e int
exec @.=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:
> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages.
> i want to process the out put returned by the exec command in my stored proc
> Is it possible to capture the out put in addition of return code(0/1),which
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux|||declare @.e int
exec @.e=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:
> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages.
> i want to process the out put returned by the exec command in my stored proc
> Is it possible to capture the out put in addition of return code(0/1),which
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux

Processing OutPut from exec command

Hi,
I am using exec command command to run xp_cmdshell ext SP.
the command line string given to xp_cmdshell is for executing DTS Packages.
i want to process the out put returned by the exec command in my stored proc
Is it possible to capture the out put in addition of return code(0/1),which
is returned by xp_cmdshell, if yes then how?
thanks in advance
Kailuxdeclare @.e int
exec @.=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:

> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages
.
> i want to process the out put returned by the exec command in my stored pr
oc
> Is it possible to capture the out put in addition of return code(0/1),whic
h
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux|||declare @.e int
exec @.e=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:

> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages
.
> i want to process the out put returned by the exec command in my stored pr
oc
> Is it possible to capture the out put in addition of return code(0/1),whic
h
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux

Processing OutPut from exec command

Hi,
I am using exec command command to run xp_cmdshell ext SP.
the command line string given to xp_cmdshell is for executing DTS Packages.
i want to process the out put returned by the exec command in my stored proc
Is it possible to capture the out put in addition of return code(0/1),which
is returned by xp_cmdshell, if yes then how?
thanks in advance
Kailux
declare @.e int
exec @.=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:

> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages.
> i want to process the out put returned by the exec command in my stored proc
> Is it possible to capture the out put in addition of return code(0/1),which
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux
|||declare @.e int
exec @.e=master.dbo.xp_cmdshell ....
select @.e
"kailux4" wrote:

> Hi,
> I am using exec command command to run xp_cmdshell ext SP.
> the command line string given to xp_cmdshell is for executing DTS Packages.
> i want to process the out put returned by the exec command in my stored proc
> Is it possible to capture the out put in addition of return code(0/1),which
> is returned by xp_cmdshell, if yes then how?
> thanks in advance
> Kailux