Uncommittable transaction is detected at the end of the batch. The transaction is rolled back

Question

I have a stored procedure, that I'm calling from a c# code using a transaction. When I run the code in C# (its a console app), instead of getting the result from catch block I'm getting an exception thrown saying:

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

C# code:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace app1
{
    class Program
    {
        static void Main(string[] args)
        {
            string res = "";
            string resDesc = "";

            res = WriteToDB(1,out resDesc);

            Console.WriteLine(res);
            Console.WriteLine(resDesc);


            Console.Read();
        }

        public static string WriteToDB(int val, out string resultDesc)
        {
            resultDesc = "";
            string result = "";
            string connectionString = ConfigurationManager.ConnectionStrings["SqlAppConnection"].ConnectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction("transcation1");
                try
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = transaction.Connection;
                        cmd.Transaction = transaction;
                        cmd.CommandText = "usp_Test_Proc";
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@check", val);
                        SqlParameter res = cmd.Parameters.Add("@result", SqlDbType.Int);
                        res.Direction = ParameterDirection.Output;
                        SqlParameter resDesc = cmd.Parameters.Add("@resultDesc", SqlDbType.VarChar, 100);
                        resDesc.Direction = ParameterDirection.Output;
                        cmd.ExecuteNonQuery();
                        result = res.Value.ToString().Trim();
                        resultDesc = resDesc.Value.ToString();
                        transaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    result = "Exception";
                    resultDesc = ex.Message;
                    transaction.Rollback();
                }
            }
            return result;
        }

    }
}

Stored procedure:

ALTER PROCEDURE [dbo].[usp_Test_Proc] (
                                            @check        int, 
                                            @result           INT output, 
                                            @resultDesc       VARCHAR(100) 
output) 
AS 
  BEGIN 
      SET nocount ON; 
      SET xact_abort ON;             

      IF @check != 0 
        BEGIN 
            BEGIN try              
                SET @result = 0; 
                SET @resultDesc = 'aa'; 
                --RAISERROR('Error from raiserror',1,1)
                THROW 99001, 'Error from throw', 1;
            END try
            BEGIN catch 
                SET @result = 1; 
                SET @resultDesc = concat('catch block',ERROR_MESSAGE()); 
            END catch; 
        END 
        ELSE
        BEGIN
                SET @result = 0; 
                SET @resultDesc = 'done'; 
                end
  END; 

GO

When the stored procedure throws an error, instead of going in to the catch block I'm getting an exception back saying "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back."

But if I run the stored procedure in SSMS it works as expected:

Why is the result different when I'm calling it from C# code?

Answer 1

It's because you have the 'set xact_abort on'.. As per documentation xact_abort is used to Specifies whether SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

Also you need to set the 'state' value to -1 in the throw so that the transaction becomes uncomitable.