SQL Error "uncommittable transaction is detected at the end of batch" without transactions on db

Question

I'm facing the following problem at different points of my code. An SqlException is returned from DB (SQL Server) with message "Uncommitable Transaction is detected at the end of the batch. Transaction is Rolled Back" after calling a Stored Procedure. Stored procedure structure follows this example:

USE [EXAMPLE_DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ExampleStoredProcedure]
@Parameter INT, @AnotherParameter INT

AS
BEGIN
    BEGIN TRY

    SET NOCOUNT ON;


    DECLARE @Variable INT;
    DECLARE @AnotherVariable CHAR;
    DECLARE @ErrMsg VARCHAR;

    SET @ErrMsg = '';

    /*Doing Some Stuff Here (Select, IF-THEN, etc...)

            /* I call another stored procedure */   
            EXECUTE [dbo].[sp_SecondStoredProcedure] 
                       @Param = 'Blabla'
                      ,@Param2 = 'BlaBlaBla'


            /*Here I handle some custom output parameters from second stored                           
                     procedure to handle errors */



                    /* Other stuff here */

END TRY

BEGIN CATCH    
        SET @CustomExitCode = 'XXXXX';
        SET @ErrMsg = (SELECT ERROR_MESSAGE()); 
END CATCH;

END

As you can see, inside stored procedure there's no transaction handling. I handle transaction code-side (C#) using TransactionScope class, calling various stored procedure inside transaction scope, including the one above (which is the one failing).

Question is: why does SQL talk about Uncommitable Transaction, if I have no transactions SQL side, but only a try-catch block, with all transaction handlings performed on the code instead?

I've already searched around the web and found a lot of materials about the Uncommittable Transaction error, but everywhere there is transaction handling on the SQL-Side, actually.

I hope I have explained the issue in an understandable manner. I'm obviously available for any further info.

Thank you very much for your help!

Cheers, Gabriele

Teucer

show your c# calling code.... – Secondrate

Answer 1

Wrap up all operations in TRY BLOCK inside a Transaction and let sql server handle it for you rather then you handling it in your application. Explicitly BEGIN TRAN and COMMIT Trans in Try Block and if anything goes wrong during this transaction roll back it.

I would do something like ......

ALTER PROCEDURE [dbo].[sp_ExampleStoredProcedure]
@Parameter INT, @AnotherParameter INT
AS
BEGIN
    SET NOCOUNT ON;
 BEGIN TRY
    DECLARE @Variable INT;
    DECLARE @AnotherVariable CHAR;
    DECLARE @ErrMsg VARCHAR;

    SET @ErrMsg = '';
BEGIN TRANSACTION;      --<-- Begin here 

    /*Doing Some Stuff Here (Select, IF-THEN, etc...)*/

            /* I call another stored procedure */   
            EXECUTE [dbo].[sp_SecondStoredProcedure] 
                       @Param = 'Blabla'
                      ,@Param2 = 'BlaBlaBla'
            /*Here I handle some custom output parameters from second stored                           
                     procedure to handle errors */

                    /* Other stuff here */
COMMIT TRANSACTION;     --<-- Commit here if nothing gone wrong

END TRY

BEGIN CATCH    
  IF (@@TRANCOUNT > 0)
   BEGIN
      ROLLBACK TRANSACTION;   --<-- Rollback if something went wrong
   END

           /*Other error logging here*/
        SELECT @CustomExitCode = 'XXXXX', @ErrMsg = ERROR_MESSAGE(); 
END CATCH;

END

Hock

Thank you for your answer M.Ali! Unfortunatly, code is part of an enterprise application and transaction handling cannot be modified. I would be very interested in understanding WHY it behaves this way. For example, any interaction between SQL transaction engine and C# transaction engine? Thank you so much anyway! – Teucer

It is not always a good solution what you wrote (M.Ali.) If there are multiple tables to handle, then it is best that the application will do the commit , after all the tables are updated/inserted correctly. (IMHO!) – Intermeddle

C# transaction engine is nothing more than a wrapper around SQL Server's transactions. It's not like C# has own way of rolling back SQL Server code. When you create TransactionScope in C#, it does an analog to "BEGIN TRAN" somewhere in the framework, and a "COMMIT TRAN" when you're going out of scope, so the behaviour of transactions is "native" to SQL Server – Modulus

C# is NOT a wrapper around SQL transaction code. When beginning a transaction in C#, even if only one SQL Server and a single SQL command are used, it initiates a distributed transaction via MSDTC. Each SQL statement typically has an implicit transaction for that command (insert, update, delete, select), it is possible that something is going haywire (I've seen this with bad triggers) that causes the statement to fail. – Dump

Answer 2

A bit late, but error handling can be very tricky and I see many people making the same mistakes. I know that stackoverflow wants me to put the complete answer in here instead of linking to an external site, but Erland Sommarskog has written the ultimate documentation on SQL error handling and he also maintains it for years already with every new sql server version. I am not going to be able to provide an answer in any detail that he has written about this, so if you want to know all the details I suggest you read his 3 part writings on the subject:

Part One – Jumpstart Error Handling

Part Two - Commands and Mechanisms

Part Three – Implementation

The short answer is that you most likely triggered some error in the call to the 2nd procedure, and then continued to use an an already invalid transaction, which results in the uncommittable transaction.

Unbar

Answer 3

If you start transaction in "code" then the transaction will be visible to the procedure as well. Otherwise, how would it actually be a real transaction?

When you combine transactions with try catch and error occurs a lot of errors (especially when using XACT_ABORT ON) will lead to "uncommittable transaction error", see XACT_STATE documentation.

On error, you have to do something about the transaction, and if you don't, it automatically rolls back when leaving the procedure.

For these and other reasons we generally avoided try catches together with transactions. Since you're using C# code, you might just as well just move the try catch to the application part, and keep the transaction handling together with XACT_ABORT ON which means it would be rolled back on error

Modulus