Transaction count after EXECUTE error

Question

I have a stored procedure that looks something like:

CREATE PROCEDURE my_procedure 
  @val_1 INT,
  @val_2 INT
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
  BEGIN TRANSACTION;

  INSERT INTO table_1(col_1, col_2)
  VALUES (@val_1, @val_2);  

  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

  DECLARE
    @ERROR_SEVERITY INT,
    @ERROR_STATE    INT,
    @ERROR_NUMBER   INT,
    @ERROR_LINE     INT,
    @ERROR_MESSAGE  NVARCHAR(4000);

  SELECT
    @ERROR_SEVERITY = ERROR_SEVERITY(),
    @ERROR_STATE    = ERROR_STATE(),
    @ERROR_NUMBER   = ERROR_NUMBER(),
    @ERROR_LINE     = ERROR_LINE(),
    @ERROR_MESSAGE  = ERROR_MESSAGE();

  RAISERROR('Msg %d,
  Line %d,
  :%s',
    @ERROR_SEVERITY,
    @ERROR_STATE,
    @ERROR_NUMBER,
    @ERROR_LINE,
    @ERROR_MESSAGE);
END CATCH

When this code is executed through the database, everything runs correctly. When execute through ADO.NET I get back the following error message:

"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_table1_table2". The conflict occurred in database "my_database", table "dbo.table_1", column 'col_1'. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. "

Is this happening because the XACT_ABORT setting is forcing a transaction from ADO.NET to be rolled back? What's the best way to go about avoiding this error?

Answer 1

you can check XACT_STATE() in your code and then commit or rollback, check it out here: Use XACT_STATE() To Check For Doomed Transactions

basically something like this will blow up

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

and when you check xact_state you can control it

BEGIN TRANSACTION TranA
    BEGIN TRY
     DECLARE  @cond INT;
     SET @cond = 'A';
    END TRY
    BEGIN CATCH
     PRINT ERROR_MESSAGE();
    END CATCH;
    IF XACT_STATE() =0
    BEGIN
     COMMIT TRAN TranA
    END
    ELSE
    BEGIN
     ROLLBACK TRAN TranA
    END

Also take a look at these two must read links Implementing Error Handling with Stored Procedures and Error Handling in SQL Server – a Background.

Answer 2

IF XACT_STATE() =0 BEGIN COMMIT TRAN TranA END

will generate erro. XACT_STATE() = 0 means there is no transaction to commit or rollback

XACT_STATE() = 1 means there is commitable transaction

XACT_STATE() = -1 means there is uncommitable transaction which will be rollbacked by Database engine at the end of current context.