Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

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.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Use XACT STATE() To Check For Doomed Transactions

From Wiki

Jump to: navigation, search

Let's say you have the following transaction

  1. BEGIN TRANSACTION TranA
  2.     BEGIN TRY
  3.      DECLARE  @cond INT;
  4.      SET @cond = 1
  5.     END TRY
  6.     BEGIN CATCH
  7.      PRINT 'a'
  8.     END CATCH;
  9.     COMMIT TRAN TranA

This first transaction runs without a problem


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


  1. BEGIN TRANSACTION TranA
  2.     BEGIN TRY
  3.      DECLARE  @cond INT;
  4.      SET @cond =  'A';
  5.     END TRY
  6.     BEGIN CATCH
  7.      PRINT 'a'
  8.     END CATCH;
  9.     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


  1. BEGIN TRANSACTION TranA
  2.     BEGIN TRY
  3.      DECLARE  @cond INT;
  4.      SET @cond = 'A';
  5.     END TRY
  6.     BEGIN CATCH
  7.      PRINT ERROR_MESSAGE();
  8.     END CATCH;
  9.     IF XACT_STATE() =0
  10.     BEGIN
  11.      COMMIT TRAN TranA
  12.     END
  13.     ELSE
  14.     BEGIN
  15.      ROLLBACK TRAN TranA
  16.     END

After running that we can see that the following message was printed

Conversion failed when converting the varchar value 'A' to data type int.

We trapped the error, rolled back the transaction and the transaction did not blow up


Contributed by: --SQLDenis 03:14, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Pitfalls

188 Rating: 2.7/5 (37 votes cast)