FIX: BEGIN TRAN After OPEN CURSOR May Not Commit

• Microsoft SQL Server, version 6.0

SYMPTOMS

When using Transact-SQL cursors, if BEGIN TRAN is issued after OPEN CURSOR, and then the cursor is closed, there can be an open transaction that cannot be removed with COMMIT TRAN or ROLLBACK TRAN.

WORKAROUND

Call BEGIN TRAN before you call OPEN CURSOR.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 2 for SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

The following script demonstrates the problem:

set nocount on go print "This works correctly - open cursor after begin tran" print "===================================================" go drop table A go create table A (col1 int primary key, col2 char(128),col3 int) go insert into A values (1,'d1',1) insert into A values (2,'d2',2) insert into A values (3,'d3',3) insert into A values (4,'d4',4) go select 'Values before opening cursor' select col1, col3 from A go declare c cursor for select * from a for update of col3 begin transaction open c fetch c update a set col3=9 where current of c close C select 'Values after closing cursor but before ROLLBACK TRAN' select col1, col3 from A rollback transaction select 'Values after closing cursor and after ROLLBACK TRAN' select col1, col3 from A deallocate c go print "This works incorrectly - open cursor before begin tran" print "===================================================" go drop table A go create table A (col1 int primary key, col2 char(128),col3 int) go insert into A values (1,'d1',1) insert into A values (2,'d2',2) insert into A values (3,'d3',3) insert into A values (4,'d4',4) go select 'Values before opening cursor' select col1, col3 from A go declare c cursor for select * from a for update of col3 open c begin transaction fetch c update a set col3=9 where current of c close C select 'Values after closing cursor but before ROLLBACK TRAN' select col1, col3 from A rollback transaction select 'Values after closing cursor and after ROLLBACK TRAN' select col1, col3 from A deallocate c go print 'Now ROLLBACK TRAN has no effect on @@trancount' go select Trancount = @@trancount go print 'doing ROLLBACK TRAN' GO rollback tran go select Trancount = @@trancount go print 'doing ROLLBACK TRAN' GO rollback tran go select Trancount = @@trancount go print 'doing ROLLBACK TRAN' GO rollback tran go select Trancount = @@trancount go print 'doing ROLLBACK TRAN' GO rollback tran go select Trancount = @@trancount go