|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Listing prepared statements...I've noticed when using Microsoft SQL Profiler, when prepared statements are in use, yields only such information as "sp_execute 3, 12, 34" etc. This contrasts with using stored procedures, whereby I get the entire "Select" statement in the profiler output. I know if I enable events in the profiler, such as TSQL>Exec Prepared SQL and TSQL>Prepare SQL, I get to see the initial preparation of the statement, and I'm able to resolve statement handle does what. (For example, in my example above, what does statement '3' actually do?) But what about if I start the profiler AFTER the statements have been prepared (and assigned to their various numeric handle numbers)? Is there a query or method by which I can display all currently-prepared SQL statements? Thanks for any help, Mark. Mark wrote:
Show quote > Hi... You might be able to have a look in master..syscacheobjects. Try > > I've noticed when using Microsoft SQL Profiler, when prepared > statements are in use, yields only such information as "sp_execute 3, > 12, 34" etc. This contrasts with using stored procedures, whereby I > get the entire "Select" statement in the profiler output. > > I know if I enable events in the profiler, such as TSQL>Exec Prepared > SQL and TSQL>Prepare SQL, I get to see the initial preparation of the > statement, and I'm able to resolve statement handle does what. (For > example, in my example above, what does statement '3' actually do?) > > But what about if I start the profiler AFTER the statements have been > prepared (and assigned to their various numeric handle numbers)? > > Is there a query or method by which I can display all > currently-prepared SQL statements? > > Thanks for any help, > Mark. filtering on the objtype column for "Prepared statement". Thanks David,
I'll give it a try. Mark. Show quote "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:OEuWHctIGHA.1028@TK2MSFTNGP11.phx.gbl... > Mark wrote: > > Hi... > > > > I've noticed when using Microsoft SQL Profiler, when prepared > > statements are in use, yields only such information as "sp_execute 3, > > 12, 34" etc. This contrasts with using stored procedures, whereby I > > get the entire "Select" statement in the profiler output. > > > > I know if I enable events in the profiler, such as TSQL>Exec Prepared > > SQL and TSQL>Prepare SQL, I get to see the initial preparation of the > > statement, and I'm able to resolve statement handle does what. (For > > example, in my example above, what does statement '3' actually do?) > > > > But what about if I start the profiler AFTER the statements have been > > prepared (and assigned to their various numeric handle numbers)? > > > > Is there a query or method by which I can display all > > currently-prepared SQL statements? > > > > Thanks for any help, > > Mark. > > You might be able to have a look in master..syscacheobjects. Try > filtering on the objtype column for "Prepared statement". > > > -- > David Gugick > Quest Software > www.quest.com > David,
Again, thank you very much, fantastic! Yes, you're right, there in syscacheobjects is the SQL statement and dbid related to prepared statements. Mark. Show quote "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:OEuWHctIGHA.1028@TK2MSFTNGP11.phx.gbl... > Mark wrote: > > Hi... > > > > I've noticed when using Microsoft SQL Profiler, when prepared > > statements are in use, yields only such information as "sp_execute 3, > > 12, 34" etc. This contrasts with using stored procedures, whereby I > > get the entire "Select" statement in the profiler output. > > > > I know if I enable events in the profiler, such as TSQL>Exec Prepared > > SQL and TSQL>Prepare SQL, I get to see the initial preparation of the > > statement, and I'm able to resolve statement handle does what. (For > > example, in my example above, what does statement '3' actually do?) > > > > But what about if I start the profiler AFTER the statements have been > > prepared (and assigned to their various numeric handle numbers)? > > > > Is there a query or method by which I can display all > > currently-prepared SQL statements? > > > > Thanks for any help, > > Mark. > > You might be able to have a look in master..syscacheobjects. Try > filtering on the objtype column for "Prepared statement". > > > -- > David Gugick > Quest Software > www.quest.com >  |
|||||||||||||||||||||||
Other interesting topics