July 26, 2011 at 3:22 am
Is there any way to capture parameter values passed internally for nested stored procedures? Profiler is not capturing the parameters for internal invocations.
Thanks.
July 26, 2011 at 3:59 am
I see that you need to capture the parameter that is passed to a stored proc which is nested... the question is where do you want to capture it or you just want to see the values that are being passed whilst execution
July 26, 2011 at 4:29 am
you can run profiler using filters for the database specific and user specific(to make less entries in profiler) and then run the stored procedure using the user you mentioned in filter.
Select the events as :-
1) SP:stmtstarting
2) SP:stmtcompleted
3) RPC:Completed
4) SQL:stmtstarting
5) SQL:stmtcompleted
PS:- you can ignore putting filter on username if you not sure which user to connect with
----------
Ashish
July 26, 2011 at 4:53 am
crazy4sql (7/26/2011)
you can run profiler using filters for the database specific and user specific(to make less entries in profiler) and then run the stored procedure using the user you mentioned in filter.Select the events as :-
1) SP:stmtstarting
2) SP:stmtcompleted
3) RPC:Completed
4) SQL:stmtstarting
5) SQL:stmtcompleted
PS:- you can ignore putting filter on username if you not sure which user to connect with
The above mentioned settings give the name of the variable used not the value.
July 26, 2011 at 10:40 pm
I just want to see the values that are passed during execution.
July 26, 2011 at 11:09 pm
the easiest option would be to use the debug feature available in SSMS or you can print/select the variable values before the nested SP is called. Also I believe it can be done using server side tracing as well...
July 27, 2011 at 1:44 am
The above mentioned settings give the name of the variable used not the value.
I have just created the below procedure and tested it in profiler.
create procedure t111 @id int
as
select * from sys.databases where database_id = @id
Then executed "exec t111 2" in another sql windows
and if you run the profiler, in textdata you will get the value as
exec t111 2
where 2 is nothing but the value passed in parameter.
----------
Ashish
July 27, 2011 at 2:46 am
@Ashish
Can you try this nested stored proc and let me know the result.
create proc testproc (@testval nvarchar(50))
as
begin
select @testval
end
create proc testproc1 (@testval nvarchar(50))
as
begin
exec testproc @testval
end
exec testproc1 'rr'
When I ran this, In the profiler i saw exec testproc @testval and not 'rr'
July 27, 2011 at 3:24 am
create proc testproc (@testval nvarchar(50))
as
begin
select @testval
end
when i executed "exec testproc 'ashish'"
result in profile in column TextData :-
exec testproc 'ashish'
create proc testproc1 (@testval nvarchar(50))
as
begin
exec testproc @testval
end
exec testproc1 'rr'
When I ran this, In the profiler i saw exec testproc @testval and not 'rr'
result in profile in column TextData :-
exec testproc1 'rr'
I hope you know how to run the profiler and how to capture the things. If yes then run your code yourself and test it.
----------
Ashish
July 27, 2011 at 4:19 am
I hope you know how to run the profiler and how to capture the things. If yes then run your code yourself and test it.
Hi Ashish, I am very well aware of profiler an how to capture trace events... I expected you to run the outer procedure and observe the trace. For your ease please find the screenshot of the trace
You can see that I had executed stored proc TestProc1[Which is the outer Proc] and not TestProc[Which is nested inside TestProc1]
July 27, 2011 at 4:58 am
SQL Profiler is a tracing tool, so it does show what has been executed. It's not a debugging tool.
So you will never see what the parameters values are if the values were not stated in the executed query. Whould you expect profiler to show the value of the variable if it's used in the WHERE clause or anywhere else in a query?
If you want to capture the input parameter values, you should write custom logging code to do so.
July 27, 2011 at 5:43 am
Eugene Elutin (7/27/2011)
SQL Profiler is a tracing tool, so it does show what has been executed. It's not a debugging tool.So you will never see what the parameters values are if the values were not stated in the executed query. Whould you expect profiler to show the value of the variable if it's used in the WHERE clause or anywhere else in a query?
If you want to capture the input parameter values, you should write custom logging code to do so.
Well... that was something i was trying to explain... Nailed it Eugene
August 31, 2011 at 1:41 pm
Did anyone help you with this? I am having the same problem. Do you really have to add logging into the procedure itself? This seems ridiculous.
September 1, 2011 at 10:11 am
It seems there is no other way other than adding additional logging or working in a drill down mode starting from root stored procedure to the internal child procedure calls.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply