How to determine SQL that corresponds to SQL Server's sp_unprepare call?

Question

This is probably quite basic, so bear with me (on the other hand there's probably a nice shiny cut-and-dried answer!).

I'm diagnosing a deadlocking problem at the moment, and indeed I can see that one of my sessions is being blocked by another. (The other end of the deadlock are Java threads waiting on each other in the opposite order.) Showing details of the processes in Management Studio's process explorer gives me the SQL for the blocked session, but the SQL for the block*ing* session shows up solely as "EXEC sp_unprepare 807".

Now I understand that this is related to prepared statements and so I'm not perturbed by this in itself. However, I would like to know what the actual SQL was so I know where to cast a suspicious eye in the codebase. So at this point, what is the best way to correlate this to the actual SQL that was executed by this thread? Is there a system table where I can look up a mapping of prepared statements to their SQL? Perhaps a table that stores the last n SQL statements for a session that will hopefully hold the prepare call? Is there a flag I can set on the database driver connection that will disable prepared statements altogether for this session?

I'd also welcome alternative approaches to this problem, if they would be a better way to do it (basically I highly suspect there's some Java code that's failing to commit after modifying a table, and I'd like to know the SQL to help me find out where it is).

Answer 1

Try this

dbcc inputbuffer(spid)

Answer 2

Use SQL Profiler to trace activity on the server, then recreate the problem. When using server-side cursors, SQL will be using numbers as handles. You can trace these handles through the Profiler output to see what else is being executed using that handle. Between the handle and the SPID you can trace workload and explore the cause of the problem in depth.