INF: Server Side Cursors Remain Open when CURSOR_CLOSE_ON_COMMIT Set OFF

ID: Q199294

The information in this article applies to:

SUMMARY

In SQL Server version 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 Service Pack 1 for Microsoft SQL Server version 7.0, 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.

The Microsoft SQL Server version 7.0 Service Pack 1 behavior is a design and behavior change from SQL Server version 7.0 to match the SQL Server 6.5 behavior.


MORE INFORMATION

Turning trace flag 7525 ON reverts to SQL Server version 7.0 behavior of closing non-static 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.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server version 7.0. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

Additional query words: SQL_PRESERVE_CURSORS SQLEndTran SQLTransact CURSOR_CLOSE_ON_COMMIT 


Keywords          : SSrvProg kbDatabase kbODBC kbSQLServ700 
Version           : WINDOWS:3.5,3.6,3.7; winnt:7.0
Platform          : WINDOWS winnt 
Issue type        : kbinfo

Last Reviewed: June 7, 1999