API Cursor Parameters

• Ray Herring

  SSCertifiable

  Points: 5533

  More actions

  May 13, 2009 at 7:17 am

  #99690

  I support a dozen developers who use C++ Accessors and XML update grams

  extensively. When I capture trace event data with Profiler I have a lot of

  trouble finding what they are trying to do (and where :)) because most of the

  queries are sequences similar to sp_CursorOpen, sp_CursorFetch,

  sp_CursorOption, sp_Cursor, sp_CursorFetch, sp_Cursor, sp_CursorClose. I

  need to find documentation (preferably a complete reference) on the

  parameters and options associated with these calls so I can explain to the

  developers what is happening.

  For Example, what does "exec sp_cursor , 40, 1" mean? How about "exec

  sp_cursorOption , 3, 4" or "exec sp_cursorFetch 32, 1, 1?

  I have been all over Source Forge, MSDN, Technet, Support.Microsoft etc and all I find are comments about what the procedures are not descriptions of how they are used 🙁

  Thanks

• Adam Wilbur

  SSCertifiable

  Points: 7001

  More actions

  May 13, 2009 at 12:15 pm

  #993967

  All those sp_ procs are symbolic of use of ADO recordsets / datasets in code on the front end. Meaning the sp_CursorFetch will show up in a trace every time in code someone is fetching the next row in a recordset.

  Hope this helps a bit.

  
  
  A.J.
  DBA with an attitude

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  May 13, 2009 at 12:19 pm

  #993970

  Hi Roy

  Those procedures are usually not used directly from your code. They are called by your developers connection layer (OLEDB/ODBC/ADO). These are server side cursors. The server side cursors do not return all data in one bulk to client side but create temporary tables on server. With sp_fetchnext the next row will be transferred to the client.

  A little more detailed information:

  http://msdn.microsoft.com/en-us/library/ms187088.aspx

  Greets

  Flo