Capturing dynamic SQL issued from a sproc
-
Hello,
I have a sproc that issues some dynamic SQL. I want to see what SQL actually gets executed.
I ran Profiler but the only thnig it captured was my execution of the sproc, not the dynamic SQL.
Is there any way to capture this?
Many thanks in advance!
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Are you building a string and then executing it with exec()? I would insert this string into a logtable before executing it. But this is probably not what you are looking for...
-
Yeah that's exactly right Jesper. Its only for debugging purposes so dropping it elsewhere is a little bit of overkill. however if that's the only option...so be it!
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
What's the problem with the profiler?? The code being executed is the code you see there isn't it?
-
Hi Remi,
Let me give you a simple example. I have the following sproc:
create proc MyProc as
declare @STR as varchar(100)
set @STR = 'select * from sysobjects'
select * from sysobjects
exec (@str)
If I then set Profiler running and issue "exec MyProc" I only get one thing in Profiler (i.e. "exec MyProc"). I don't get anythnig indicating "select * from sysobjects" or "exec(@str)" or (even better) "exec('select * from sysobjects')
Does that make sense?
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Yup... You can always print the statement if you just want to see it.
-
Yeah, that's what I'm doing. I routinely add a BIT parameter to my sprocs (default to 0) to indicate whether to print out the dynamic SQL or not!
It would just be nice to get it in Profiler that's all.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
You should be able to see every line that is executed in a stored procedure if you have all of the stored procedure events turned on when creating the trace.
It should show the line "exec (@ssql)" or however you have executed the statement followed by a line with all of the statements actually executed prefixed with the comment -- Dynamic SQL.
-
Hey, that's very cool. SP:StmtCompleted did the job!
There was one anomoly. When I did "exec (@sql1)" then I did indeed see all the statements executed along with the comment "--Dynamic SQL". However I had another call, "exec (@sql1 + @sql2)" for which I DIDN'T see the dynamic SQL.
I'm not necassarily saying its because I had 2 variables in there, but that's going to be my first starting point for investigation. If I find anything out I'll reply here.
Thanks Andy
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
The anomoly is nothing to do with concatenating strings in the exec() function. I know because when I executed the following:
- declare @str1 varchar(100), @str2 varchar(100), @str3 varchar(100)
- set @str1 = 'select * from sysobjects'
set @str2 = 'select * from '
set @str3 = ' sysobjects'
- exec(@str1)
exec(@str2 + @str3)
I could see both dynamic sql statements in Profiler.
So, I don't understand why I can't see the dynamic sql in Profiler from the code that originally caused me to start this thread. Perhaps its because the total length of all my concatenated strings is greater than 8000. That's the only thing I can think of!
Weird!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
now what if the statement you are trying to capture is not dynamic SQL but an Update statement that is using parameters.
If I wanted to see the update statement that ran and put that into a tracking table all within the same proc... is that possible? or do I have to build the statement myself?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply