CODEWITHSUNDEEP
sql-serversql-server-2005transactionsrollback
Patrik Simons
Patrik Simons

Reputation: 23

SQL Server: rollback after insert that contains an output inserted fails mysteriously

A rollback after an insert that contains an output statement fails with "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." If the output statement is removed, then it works. Is there an explanation for this behavior?

Example:

create table test(i integer primary key)
go
begin transaction
  insert into test (i) values (1)
  insert into test (i) output inserted.i values (1)
go
rollback -- Fails 
go

begin transaction
  insert into test (i) values (1)
  insert into test (i) values (1)
go
rollback -- Works
go

Upvotes: 1

Views: 1760

Answers (2)

Pam Lahoud
Pam Lahoud

Reputation: 1105

FYI this works fine in SQL 2008, so it must have been corrected at some point.

Upvotes: 1

gbn
gbn

Reputation: 432230

I don't know why this happens. It looks like SET XACT_ABORT ON is being set implicitly

As a workaround on SQL Server 2005 SP3 we can do this if it's blocking you

create table test(i integer primary key)
go
DECLARE @foo TABLE (i int)
begin TRANSACTION
  insert into test (i) values (1)
  insert into test (i) output inserted.i INTO @foo values (1)
GO
rollback --OK
GO

Edit: It could be that the OUTPUT clause is undefined

Upvotes: 2

Related Questions