Fetch API cursor Blocking

USE [msdb]

GO

/****** Object:  StoredProcedure [dbo].[P_Cursor_Query]    Script Date: 28/05/2015 16:11:19 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_Cursor_Query]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[P_Cursor_Query]

GO

/****** Object:  StoredProcedure [dbo].[P_Cursor_Query]    Script Date: 28/05/2015 16:11:19 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_Cursor_Query]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE PROCEDURE [dbo].[P_Cursor_Query](@job_name INT = 5000)

AS

BEGIN

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[T_CURSOR_QUERY]'') AND type in (N''U''))

BEGIN

CREATE TABLE [dbo].[T_CURSOR_QUERY](

       [session_id] [int] NOT NULL,

       [properties] [nvarchar](128) NOT NULL,

       [creation_time] [datetime] NOT NULL,

       [is_open] [bit] NOT NULL,

       [text] [nvarchar](max) NULL,

       [BlockingSessionID] [nvarchar](max) NULL,

       [BlockingSQL] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

SET NOCOUNT ON;

DECLARE @BlockingSessionId INT

DECLARE @BlockingSQL nvarchar(MAX)

DECLARE @SessionIDList TABLE (id INT identity(1,1), BlockingSessionId INT NOT NULL,BlockingSQL nvarchar(MAX) NULL)

DECLARE @count int

SELECT

              Blocking.session_id as BlockingSessionId

              , BlockingSQL.text AS BlockingSQL

              , Blocked.session_id AS BlockedSessionId

              , BlockedSess.login_name AS BlockedUser

              , BlockedSQL.text AS BlockedSQL

              , DB_NAME(Blocked.database_id) AS DatabaseName into #temp

              FROM MASTER.SYS.dm_exec_connections AS Blocking

              INNER JOIN MASTER.SYS.dm_exec_requests AS Blocked

              ON Blocking.session_id = Blocked.blocking_session_id

              INNER JOIN MASTER.SYS.dm_exec_sessions Sess

              ON Blocking.session_id = sess.session_id

              INNER JOIN MASTER.SYS.dm_tran_session_transactions st

              ON Blocking.session_id = st.session_id

              LEFT OUTER JOIN MASTER.SYS.dm_exec_requests er

              ON st.session_id = er.session_id

              AND er.session_id IS NULL

              INNER JOIN MASTER.SYS.dm_os_waiting_tasks AS Waits

              ON Blocked.session_id = Waits.session_id

              CROSS APPLY MASTER.SYS.dm_exec_sql_text(Blocking.most_recent_sql_handle)

              AS BlockingSQL

              INNER JOIN MASTER.SYS.dm_exec_requests AS BlockedReq

              ON Waits.session_id = BlockedReq.session_id

              INNER JOIN MASTER.SYS.dm_exec_sessions AS BlockedSess

              ON Waits.session_id = BlockedSess.session_id

              CROSS APPLY MASTER.SYS.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL

              WHERE Waits.wait_duration_ms > @job_name AND BlockingSQL.text LIKE ''FETCH API_CURSOR%'';

       SELECT @count = 1

       INSERT INTO @SessionIDList

       SELECT BlockingSessionId,BlockingSQL FROM #TEMP

       WHILE @count <= (SELECT MAX(id) FROM @SessionIDList)                      

              BEGIN                     

             

              SELECT @BlockingSessionId=BlockingSessionId, @BlockingSQL=BlockingSQL FROM @SessionIDList

              WHERE ID = @count

             

              SELECT @BlockingSessionId AS BlockingSessionID, @BlockingSQL as BlockingSQL

                          

              INSERT INTO T_CURSOR_QUERY (session_id, properties, creation_time, is_open, [text],BlockingSQL,BlockingSessionId )

              SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text, @BlockingSQL  , @BlockingSessionId

              FROM sys.dm_exec_cursors (@BlockingSessionId) c

              CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t ;

              SELECT @count = @count + 1                                                              

      

              END   

             

              IF Exists (SELECT TOP 1 creation_time FROM  MSDB.DBO.T_CURSOR_QUERY (NOLOCK)

               WHERE datediff(dd,creation_time,getdate())>7)

      

       BEGIN

              DELETE FROM MSDB.DBO.T_CURSOR_QUERY

              WHERE datediff(dd,creation_time,getdate())>7;

       END

SET NOCOUNT OFF;                                                                 

END

'

END

GO