Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

SET CURSOR_CLOSE_ON_COMMIT

SQL Server 2000

Controls whether or not a cursor is closed when a transaction is committed.

Syntax

SET CURSOR_CLOSE_ON_COMMIT { ON | OFF }

Remarks

When SET CURSOR_CLOSE_ON_COMMIT is ON, this setting closes any open cursors on commit or rollback in compliance with SQL-92. When SET CURSOR_CLOSE_ON_COMMIT is OFF, the cursor is not closed when a transaction is committed.

When SET CURSOR_CLOSE_ON_COMMIT is OFF, a ROLLBACK statement closes only open asynchronous cursors that are not fully populated.STATIC or INSENSITIVE cursors that were opened after modifications were made will no longer reflect the state of the data if the modifications are rolled back.

SET CURSOR_CLOSE_ON_COMMIT controls the same behavior as the cursor close on commit database option of sp_dboption. If CURSOR_CLOSE_ON_COMMIT is set to ON or OFF, that setting is used on the connection. If SET CURSOR_CLOSE_ON_COMMIT has not been specified, the cursor close on commit setting of sp_dboption applies.

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. DB-Library does not automatically set the CURSOR_CLOSE_ON_COMMIT value.

When SET ANSI_DEFAULTS is ON, SET CURSOR_CLOSE_ON_COMMIT is enabled.

The setting of SET CURSOR_CLOSE_ON_COMMIT is set at execute or run time and not at parse time.

Permissions

SET CURSOR_CLOSE_ON_COMMIT permissions default to all users.

Examples

This example defines a cursor in a transaction and attempts to use it after the transaction is committed.

SET NOCOUNT ON

CREATE TABLE t1 ( a int )
GO 

INSERT INTO t1 values (1)
INSERT INTO t1 values (2)
GO

PRINT '-- SET CURSOR_CLOSE_ON_COMMIT ON'
GO
SET CURSOR_CLOSE_ON_COMMIT ON
GO

PRINT '-- BEGIN TRAN'
BEGIN TRAN

PRINT '-- Declare and open cursor'
DECLARE testcursor CURSOR FOR
SELECT a 
FROM t1

OPEN testcursor

PRINT '-- Commit tran'
COMMIT TRAN

PRINT '-- Try to use cursor'

FETCH NEXT FROM testcursor

CLOSE testcursor
DEALLOCATE testcursor
GO


PRINT '-- SET CURSOR_CLOSE_ON_COMMIT OFF'
GO
SET CURSOR_CLOSE_ON_COMMIT OFF
GO


PRINT '-- BEGIN TRAN'
BEGIN TRAN

PRINT '-- Declare and open cursor'
DECLARE testcursor CURSOR FOR
SELECT a 
FROM t1

OPEN testcursor

PRINT '-- Commit tran'
COMMIT TRAN

PRINT '-- Try to use cursor'

FETCH NEXT FROM testcursor

CLOSE testcursor
DEALLOCATE testcursor
GO

DROP TABLE t1
GO

See Also

BEGIN TRANSACTION

CLOSE

COMMIT TRANSACTION

ROLLBACK TRANSACTION

SET

SET ANSI_DEFAULTS

Setting Database Options

sp_dboption

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.