CODEWITHSUNDEEP
sql-servercursorsql-server-2012
LoZeno
LoZeno

Reputation: 148

Declaring insensitive cursor in sql server 2012 with sp_executesql

I have a stored procedure that makes use of cursors; it was written in SQL Server 2005, and worked fine until SQL Server 2008.

We recently decided to move to SQL Server 2012, and tried to move all our databases to the new environment, but this stored procedure fails on opening the cursor.

Here is the part:

BEGIN TRY
        SELECT @SqlStatement =  'DECLARE cursorMoveStatements  INSENSITIVE CURSOR  FOR ' 
                                + 'Select [name],[status] From  [' + @ServerName 
                                +'].['+ @DBName 
                                + '].sys.sysfiles FOR READ ONLY'
        EXEC sp_executesql @SqlStatement

            OPEN cursorMoveStatements
    ..... --Do Some Stuff
        END TRY

@ServerName and @DBName are appropriately declared and set earlier. When run, the error indicates that cursorMoveStatements is not set when I try to open it.

I know that I can set a variable containing the cursor, then pass it to the sp_executesql and get the set cursor via its OUTPUT keyword, but that would force me to rewrite large parts of this SP (which is really, really long). I wonder what has changed in SQL Server that makes this part (which works fine in older versions of SQL) fail. I've searched MSDN and couldn't find a clue on what is different now.

Any help, if not to "fix" this, at least to let me understand what's different in SQL Server 2012 that makes this not work?

Upvotes: 1

Views: 2266

Answers (1)

ta.speot.is
ta.speot.is

Reputation: 27214

Your answer might be in the documentation for cursor name scope. Specifically, the information about CURSOR_DEFAULT:

The database option CURSOR_DEFAULT, set with the ALTER DATABASE statement, controls the default taken by the DECLARE CURSOR statement if neither LOCAL nor GLOBAL is specified.

Your new SQL Server 2012 database probably has a different CURSOR_DEFAULT than your SQL Server 2005 database.

ALTER DATABASE SET Option describes how CURSOR_DEFAULT works in more detail:

CURSOR_DEFAULT { LOCAL | GLOBAL }
Controls whether cursor scope uses LOCAL or GLOBAL.

LOCAL
When LOCAL is specified and a cursor is not defined as GLOBAL when created, the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. The cursor is implicitly deallocated when the batch, stored procedure, or trigger ends, unless it was passed back in an OUTPUT parameter. If the cursor is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable that references it is deallocated or goes out of scope.

GLOBAL
When GLOBAL is specified, and a cursor is not defined as LOCAL when created, the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection.

Upvotes: 1

Related Questions