How to understand this SQL statement "FETCH API_CURSOR00000000004913E3" ?

  • When I used dbcc inputbuffer(blocked SPID) and it showed "FETCH API_CURSOR00000000004913E3" this statement,normally, when we use dbcc inputbuffer(blocked spid), it should show a select?insert?update ?delete ?create ?drop or alter related statement, but this time it showed this statement, it seems that this is a cursor related statement, but how can I know the real SQL statement related this "FETCH API_CURSOR00000000004913E3"; many thanks!

    • This topic was modified 4 years, 6 months ago by  892717952.
  • Well, FETCH is also an SQL statement.

    Apparently this is a cursor set up in the API, and I will have to admit that I don't know how to find the SELECT query that drives the cursor. Which depending on the cursor style is of interest or not. If the cursor is static, the result for the cursor is in a worktable is in tempdb. But if it is a dynamic cursor, every FETCH means data access.

    My beta_lockinfo, available at http://www.sommarskog.se/sqlutil/beta_lockinfo.html, can give some information so far that you can see which locks that are being help. With some knowledge about the application, you may be able to draw some conclusions.

    And, no, API cursors is nothing I like. Or recommend.

    • This reply was modified 4 years, 6 months ago by  Erland Sommarskog. Reason: Missing word

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Thank you Erland Sommarskog kind help!

    Yes, Fetch is also a T-SQL Statement, and fetch is used for a cursor, and I just now to know which select statement  is associated with this fetch action, thanks!

     

  • 892717952 wrote:

    When I used dbcc inputbuffer(blocked SPID) and it showed "FETCH API_CURSOR00000000004913E3" this statement,normally, when we use dbcc inputbuffer(blocked spid), it should show a select?insert?update ?delete ?create ?drop or alter related statement, but this time it showed this statement, it seems that this is a cursor related statement, but how can I know the real SQL statement related this "FETCH API_CURSOR00000000004913E3"; many thanks!

    Rather than me regurgitating what I know, here's the post that mimics the way I do it (taught myself during a fortunate accident a long time ago) and also has a secondary method for "after the fact" finds.

    https://www.sqlskills.com/blogs/joe/hunting-down-the-origins-of-fetch-api_cursor-and-sp_cursorfetch/

    ... and, yeah... both methods get you to the original code and the good ol' SQL Profiler method (which can also be done using extended events) can sometimes get you back to the login and host name.

    Heh... and, no... these things are definitely NOT T-SQL related cursors.

    Meaning only the best for you, especially for your future, I had lost the article I was looking for due to multiple machines coming and going.  So I tried a web search for "FETCH API_CURSOR" (without the quotes) and the article I pointed to you above was the first thing presented.  That's a hint for your future success. 😉

    https://www.google.com/search?q=FETCH+API_CURSOR

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you Jeff Moden kind's help!

    your post is very valuable and helpful!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply