Geek Answers Handbook

Is there a way to get a list of open / selected cursors on a SQL server?

I have a stored procedure that creates and opens some cursors. He closes them at the end, but if he is mistaken, these cursors remain open! Then subsequent runs fail when it tries to create cursors, because the cursor with the name already exists.

Is there a way I can ask which cursors exist, and if they are open or not, can I close and release them? I feel that it’s better than blindly trying to close and learn the mistakes.

+5
source share
4 answers

This seems to work for me:

CREATE PROCEDURE dbo.p_cleanUpCursor @cursorName varchar(255) AS
BEGIN

    DECLARE @cursorStatus int
    SET @cursorStatus =  (SELECT cursor_status('global',@cursorName))

    DECLARE @sql varchar(255)
    SET @sql = ''

    IF @cursorStatus > 0
        SET @sql = 'CLOSE '+@cursorName

    IF @cursorStatus > -3
        SET @sql = @sql+' DEALLOCATE '+@cursorName

    IF @sql <> ''
        exec(@sql)

END
+1
source

, . , , Row By Agonizing Row.

sp

  • ( - RBAR)

  • , , . . 1 2

SQL2005, try catch

EDIT ( ): , , .

Red Gate SQL, ( , , FK [] ).

+2

2008R2, :

USE MASTER
GO
select s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name
, c.cursor_id, c.properties, c.creation_time, c.is_open, con.text,
l.resource_type, d.name, l.request_type, l.request_Status, l.request_reference_count, l.request_lifetime, l.request_owner_type
from sys.dm_exec_cursors(0) c
left outer join (select * from sys.dm_exec_connections c cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) mr) con on c.session_id = con.session_id
left outer join sys.dm_exec_sessions s on s.session_id = c.session_id
left outer join sys.dm_tran_locks l on l.request_session_id = c.session_id
left outer join sys.databases d on d.database_id = l.resource_database_id
+2

sp_cursor_list, , , sp_describe_cursor, sp_describe_cursor_columns sp_describe_cursor_tables, .

( http://msdn.microsoft.com/it-it/library/aa172595(v=sql.80).aspx)

0

More articles:


All Articles