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 STATE() To Check For Doomed Transactions

From Wiki

Jump to: navigation, search

Let's say you have the following transaction

tsqlLine number Off | Hide | Select all

BEGIN TRANSACTION TranA
BEGIN TRY
DECLARE @cond INT;
SET @cond = 1
END TRY
BEGIN CATCH
PRINT 'a'
END CATCH;
COMMIT TRAN TranA

This first transaction runs without a problem

Now change value of @cond from 1 to 'A' and run this code below.

tsqlLine number Off | Hide | Select all

BEGIN TRANSACTION TranA
BEGIN TRY
DECLARE @cond INT;
SET @cond = 'A';
END TRY
BEGIN CATCH
PRINT 'a'
END CATCH;
COMMIT TRAN TranA

This transaction will blow up with the following message Server: Msg 3930, Level 16, State 1, Line 15 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Server: Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

We can use XACT_STATE() to check if we need to rollback or not without blowing up. I also used the ERROR_MESSAGE() function to print the error

tsqlLine number Off | Hide | Select all