Can I get a Query from a Fetch_cursor in SQL2000

Hot Topic

SqlBackUp() vs Volume Shadow Copy Service
Accessing Variables in SSIS code
count of null values in columns
How to reference a dataset passed to script
SP2 - Need Your Feedback!
Need help on COM-based Custom Conflict Resolver
Sumary of Table rows from System Tables/Views
Agents not appearing in Replication Monitor (SQL 2000)
Authentication issue
Need Help on this SQL Statement

SQL Server

Error:sql mobile encountered problems when opening the database
value is null remotely - non null locally
Data retrieval is much slower in RS than in management studio
top n'th within a group
Fromatting excel column
long running report can not be rendered in Report Manager or through Subscription
SQL Server Setup Fails to Complete
Problem in changing Account Name, Password
Problem with Multi-value parameters
Webparts connection to sql 2000

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



Answer this question

Can I get a Query from a Fetch_cursor in SQL2000

  • Praveen_wm

    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.

  • MaggieChan

    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.


  • R.Tutus

    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.



  • None01

    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:)
  • Can I get a Query from a Fetch_cursor in SQL2000
Answer this question