Can I get a Query from a Fetch_cursor in SQL2000

Hi, The main application we used uses prepared cursors when running statments against the database. This makes it very hard to debug the querys as if you get an error or lock on the database all you can see is the Fetch_curror statement and the number of the statment. If you know the lock is coming that's fine as you can run up the profiler but if you don't know it will happen then the profiler doesn't help as the cursor has already been prepared.

Is there a way of using the cursor number to query the SQL engine and find out what statment was prepared?

If your app is doing server side cursors and names them then you may be able to use sp_cursorlist, sp_describe_cursor_tables and sp_describe_cursor_columns to better understand what the query is doing. Then you can query the sysCacheObjects table in the master database and it will show you the first 128 characters of the batch (on 2005 it will show you the first 3900 characters) of cached plans. You can use the ObjType column to distinguish between ad-hoc, prepared, stored procedures etc.

See BOL for more info.

|||

Thanks fro the reply David but it still doesn't give me the query that was run (the TSQL), if all I had was the fetch_cursor id.

The sysCacheObjects is cool, I can use this for other things:)

|||True, you can't query the sysCacheObjects table just based on the cursor_id but I thought it would be the next best thing, since it gives you a place to look for cached plans and the queries that sparked them.|||

It is not possible to get the query with just the cursor handle in SQL Server 2000. You could query syscacheobjects if you know the prepare cursor call or a part of the SELECT statement etc. You will have to rely on SQL Profiler or application trace to get the calls.

In SQL Server 2005, you can use the new dynamic management function sys.dm_exec_cursors to better determine cursor handles on the server, their associated sql handles to get query text and other information.

sql