Microsoft KB Archive/264901

= BUG: Error 16917 When CURSOR_CLOSE_ON_COMMIT Is Set ON =

Article ID: 264901

Article Last Modified on 11/14/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q264901



BUG #: 57967 (SQLBUG_70)

BUG #:231137 (SHILOH)



SYMPTOMS
When CURSOR_CLOSE_ON_COMMIT is set ON, such system stored procedures as sp_helpindex and sp_helptext may return the following error message:

Server: Msg 16917, Level 16, State 2, Line 0

Cursor is not open.



CAUSE
The CURSOR_CLOSE_ON_COMMIT setting applies to user as well as implicit transactions. All Data Definition Language (DDL) and Data Manipulation Language (DML) statements start implicit transactions. Once this transaction is committed, the cursor is closed as per this setting. In addition, some of the system stored procedures insert into the temporary table by using a fetch statement, and therefore start an implicit transaction.



WORKAROUND
To work around this problem you can use sp_dboption at the database level to set CURSOR_CLOSE_ON_COMMIT to OFF.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.

Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
When CURSOR_CLOSE_ON_COMMIT is ON, all open cursors are closed on commit in compliance with SQL-92.

The above information also applies to user stored procedures with cursors. DB-Library applications are affected the most because DB-Library does not automatically set the CURSOR_CLOSE_ON_COMMIT value. The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver both set CURSOR_CLOSE_ON_COMMIT to OFF when they connect.

To reproduce the problem, run the following from Query Analyzer: use master go set cursor_close_on_commit ON go sp_helpindex sysdatabases

Additional query words: cursor stored procedures dblib 16917

Keywords: kbbug kbpending KB264901

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.