INF: Server Side Cursor Behavior When CURSOR_CLOSE_ON_COMMIT Set OFF

Article translations Article translations
Article ID: 199294 - View products that this article applies to.
This article was previously published under Q199294
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

SUMMARY

In SQL Server 7.0, when CURSOR_CLOSE_ON_COMMIT is set OFF, a ROLLBACK statement closes any Transact-SQL cursor defined with the SQL-92 form of the DECLARE CURSOR statement, unless the DECLARE CURSOR statement contains either the INSENSITIVE or STATIC keywords. All application programming interface (API) server cursors are also closed unless they have been defined as STATIC cursors (such as using the ODBC SQL_CURSOR_STATIC attribute).

In SQL Server 7.0 Service Pack 1 and Service Pack 2, and in SQL Server 2000, when CURSOR_CLOSE_ON_COMMIT is set OFF, a ROLLBACK statement does not close a Transact-SQL cursor defined with the DECLARE CURSOR statement. Server cursors opened through database API functions are also left open after a ROLLBACK statement. This behavior is a design and behavior change from SQL Server 7.0 to match the SQL Server 6.5 behavior.

MORE INFORMATION

Turning trace flag 7525 ON reverts to the SQL Server 7.0 behavior of closing nonstatic cursors regardless of CURSOR_CLOSE_ON_COMMIT's state.

From ODBCAPI, SQL_COPT_SS_PRESERVE_CURSORS defines the behavior of cursors when manual-commit mode is used.

SQLSetConnectOption(hdbc, SQL_PRESERVE_CURSORS, SQL_PC_ON) can be used to preserve cursors.

Properties

Article ID: 199294 - Last Review: February 23, 2014 - Revision: 4.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft ODBC Driver for Microsoft SQL Server 3.5
  • Microsoft ODBC Driver for Microsoft SQL Server 3.6
  • Microsoft ODBC Driver for Microsoft SQL Server 3.7
Keywords: 
kbnosurvey kbarchive kbdatabase kbinfo KB199294

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com