title: Declaring insensitive cursor in sql server 2012 with sp_executesql
body: <p>I have a stored procedure that makes use of cursors; it was written in SQL Server 2005, and worked fine until SQL Server 2008.</p>
  
  <p>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.</p>
  
  <p>Here is the part:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><code>@ServerName</code> and <code>@DBName</code> are appropriately declared and set earlier. When run, the error indicates that cursorMoveStatements is not set when I try to open it.</p>
  
  <p>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.</p>
  
  <p>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?</p>