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 

Additional query words: SQL6 CURSOR TRANSACTION

Keywords          : kbprg SSrvProg SSrvStProc kbbug6.00 kbfix6.00.sp2 
Version           : 6.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: April 20, 1999