View SQL prepared with sp_prepare

Question

I am troubleshooting an application that uses a SQL Server database and I am seeing a lot of sp_execute calls.

I can not seem to find the sp_prepare calls.

How can you inspect all of the prepared SQL statements in memory?

Answer 1

I was looking for a way to see the actual SQL statements executed by sp_execute in SQL Server 2008 R2 Profiler.

To do this, I created a new trace, and clicked on the "Events Selection" tab. I selected "Show all events" and checked Stored Procedures > SP:StmtCompleted. Running the trace, I was then able to see the actual SQL statements.

Answer 2

I ran into this issue as well. SQL Profiler was not capturing the sp_prepare statement because it occurred before the SQL Profiler trace had started to run. The various postings that rely on sys.dm_exec_sql_text did not help because I could not find the correct sql_handle or plan_handle value to provide for that stored procedure.

I found a solution from this blog post: in SQL Profiler, click the "Show all events" checkbox and then under the "Stored Procedures" heading choose "SP:CacheHit".

In the resulting SQL Profiler output, you'll see an "SP:CacheHit" row containing the cached SQL statement near your "RPC:Starting ... sp_execute" statement.

You can then reconstruct and reexecute the full SQL statement in SSMS if you wish using:

exec sp_executesql @stmt=N'{statement from SP:CacheHit}', 
  @params=N'{parameter declaration from  SP:CacheHit}',
  @param1={value}, {...parameters from RPC:Starting sp_execute statement}