Is there any way to get a list of open/allocated cursors in SQL server?

Question

I have a stored procedure that creates and opens some cursors. It closes them at the end, but if it hits an error those cursors are left open! Then subsequent runs fail when it tries to create cursors since a cursor with the name already exists.

Is there a way I can query which cursors exists and if they are open or not so I can close and deallocate them? I feel like this is better than blindly trying to close and swallow errors.

Answer 1

Look here for info on how to find cursors. I have never used any of them because I could figure out a way to get it done without going Row By Agonizing Row.

You should rebuild the sp to either

• not use cursors ( we can help - there is almost always a way to avoid RBAR)

• build it in a transaction and roll it back if there is a failure or if you detect an error. Here are some excellent articles on this. part 1 and part 2

If you have SQL2005, you can also use try catch

EDIT (in response to your post):Ideally, data generation is best handled at the application level as they are better suited for non set based operations.

Red Gate has a SQL Data generator that I have used before (its great for single tables, but takes some configuring if you have lots of FK or a wide [normalized] database).

Answer 2

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