BUG: Error 16917 When CURSOR_CLOSE_ON_COMMIT Is Set ON (264901)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)
Microsoft SQL Server 6.5
Microsoft SQL Server 7.0

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

Modification Type:	Major	Last Reviewed:	11/14/2003
Keywords:	kbBug kbpending KB264901