September 9, 2019 at 1:49 am
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!
September 9, 2019 at 5:58 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 10, 2019 at 3:14 am
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!
September 10, 2019 at 11:00 pm
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
Change is inevitable... Change for the better is not.
September 11, 2019 at 4:18 am
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