Profiler question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Profiler question

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2003-09-24 : 16:04:23

Hi,

In profiler we can easily trace the parameters passed to the procedure and output values....but if we have nested procedures ..how can we trace using profiler what are the input parameters passed to inner procedure from outer proc?

Thanks
--rubs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-24 : 16:15:18

You'll need to include SP:Completed event class in the Stored Procedures Events.

Tara

rubs_65
Posting Yak Master

144 Posts

Posted - 2003-09-24 : 16:57:40

Tara,

I tried option SP:Completed event class but somehow it only shows parameters passed to outer proc and not showing the parameters passed to inner proc.

Thanks
--rubs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-24 : 17:10:12

It works fine for me. Here is some code to test:



*** Create the objects for the test, insert data, select from the table
CREATE PROC usp_InnerProc
(@SomeVariable VARCHAR(50))
AS

UPDATE Table1
SET Column1 = @SomeVariable

RETURN

GO

CREATE PROC usp_OuterProc
AS

EXEC usp_InnerProc 'Someone'

RETURN

GO

CREATE TABLE Table1
(
Column1 VARCHAR(50) NOT NULL
)

INSERT INTO Table1 VALUES('Tara')

SELECT Column1
FROM Table1

*** Now setup SQL Profiler with the default events and add SP:Completed, then run the below EXEC statement
EXEC usp_OuterProc

*** Now take a look at SQL Profiler, here is what I see (EventClass and TextData column only):
EventClass TextData    
---------- ------------------------------------------------------------------ 
0          NULL
43         EXEC usp_InnerProc 'Someone'
43         EXEC usp_OuterProc
12         EXEC usp_OuterProc
5          NULL


*** To delete the objects created from this test, run:
DROP TABLE Table1
DROP PROC usp_OuterProc
DROP PROC usp_InnerProc

So what do you see in your SQL Profiler when you run the above test?

Tara

rubs_65
Posting Yak Master

144 Posts

Posted - 2003-09-24 : 17:40:18

Tara,

This works thanks a lot. But problem in our case is that in outer procedure we have some varaiables..we assign values to these variables depending upon the business rules....and then pass these variables as input parameters to inner proc..since the values of variables are dynamically generated..the proc looks like:
create proc outer
as
declare @a int
declare @b int
exec inner_proc @a, @b output

so we need to know what actual values passed to inner proc...

Thanks
--rubs

rubs_65
Posting Yak Master

144 Posts

Posted - 2003-09-24 : 17:40:19

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-24 : 18:06:14

Ah, I am able to duplicate your problem. Let me see which event class will give you what you need.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-24 : 18:11:54

It does not appear that there is any event class that is going to help you out on this one. So what you would need to do is take a look at the input variables of the outer stored procedure and manually walk through the code to see what the the input variables to the inner stored procedure would be.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources