Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Use XACT ABORT to roll back non trappable error transactions

From Wiki

Jump to: navigation, search

Let's say you have a transaction and you want to make sure that it rolls back everything even when you have a non trappable error. What can you do? You can use XACT_ABORT and set it to on. First create this table

tsqlLine number Off | Hide | Select all

CREATE TABLE LogError(ErrorID INT PRIMARY KEY)
GO

Now run this transaction

tsqlLine number Off | Hide | Select all

BEGIN TRANSACTION TranA
DECLARE @error INT
INSERT LogError VALUES(1)
SELECT @error = @@ERROR
IF @error =0
BEGIN
INSERT LogError VALUES(2)
SELECT @error = @@ERROR
END
IF @error =0
BEGIN
INSERT LogError VALUES(3)
SELECT @error = @@ERROR
END
--Will blow up here with a non trapable error
IF @error =0
BEGIN
INSERT LogError2 VALUES(1)
SELECT @error = @@ERROR
END
IF @error =0
BEGIN
COMMIT TRAN TranA
END
ELSE
BEGIN
ROLLBACK TRAN TranA
END

So the transaction blew up with the following error Server: Msg 208, Level 16, State 1, Line 22 Invalid object name 'LogError2'.

Now run this query

tsqlLine number Off | Hide | Select all

SELECT * FROM LogError

That is right the first three inserts are in the table

Delete those rows

tsqlLine number Off | Hide | Select all

DELETE LogError

If you set XACT_ABORT on before the transaction then everything will be rolled back Run this block of code

tsqlLine number Off | Hide | Select all

SET XACT_ABORT ON
BEGIN TRANSACTION TranA
DECLARE @error INT
INSERT LogError VALUES(1)
SELECT @error = @@ERROR
IF @error =0
BEGIN
INSERT LogError VALUES(2)
SELECT @error = @@ERROR
END
IF @error =0
BEGIN
INSERT LogError VALUES(3)
SELECT @error = @@ERROR
END
--Will blow up here with a non trapable error
IF @error =0
BEGIN
INSERT LogError2 VALUES(1)
SELECT @error = @@ERROR
END
IF @error =0
BEGIN
COMMIT TRAN TranA
END
ELSE
BEGIN
ROLLBACK TRAN TranA
END

Now run this query

tsqlLine number Off | Hide | Select all

SELECT * FROM LogError

That is right no rows. Keep in mind that if you run the first query again in the same query window it will rollback the three rows also since the XACT_ABORT is still set to on, you have to run this

tsqlLine number Off | Hide | Select all