Get currently executing SQL cursor

Question

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

It says the section at the beginning of the start / end offset that you can use in conjunction with dm_exec_sql_text to get the executable cursor operator. What is the team for this?

I have a rather large SP that froze when it reached the logical loop of the 'while cursor (@@ fetch_status = 0), and I would like to see if this is an exact execution operation, I think it is, and I would also like to to see the parameters for this statement, so that I can see exactly why the cursor never exited, or simply why the statement inside the loop will not be completed. Thanks!

Answer 1

start with this

SELECT * FROM(SELECT session_id, COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName, (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement FROM sys.dm_exec_requests AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x WHERE sql_statement NOT like 'SELECT * FROM(SELECT session_id,COALESCE(%'