How to get detailed error from CLR Stored Procedure called in DTC transaction from SSIS Package

Question

Whenever an exception is thrown in CLR stored procedure called from a SSIS package, within a DTC transaction , the error returned to client (SSIS package) relates to DTC rather than the underlying exception.

Is it possible to have information for the underlying error returned to client?

Note: When stored procedure is run from SQL Server Management Studio, outside of distributed transaction, detailed information for underlying error is returned.

Error: Executing the query "StoredProcedure” failed with the following error: “The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction”. Possible reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

All code running on a single SQL Server 2008 instance.

SSIS Package
---> Sequence Container (TransactionOption = Required )
---> Execute SQL Task (ADO.NET Connection Manager, SQLClient DataProvider)
---> SQL Server CLR Stored Procedure (No exception handling code)
---> TransactionScope(TransactionScopeOption.Required)

Code Which Reproduces Issue

The following code illustrates the issue but differs from the scenario described in the title in that client is a C# console app rather than a SSIS package

CLR Stored Procedure

using System;
using System.Transactions;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ExceptionStoredProcedure()
    {
        using (TransactionScope scope = new TransactionScope())
        {
            throw new Exception(@"Test exception thrown from ""ExceptionStoredProcedure""");
        }
    }
};

C# Console App Client

using System;
using System.Data.SqlClient;
using System.Transactions;

namespace SQLCLRTester
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    string connectionString = "Data Source=.; Initial Catalog=Experiments; Integrated Security=Yes";

                    using (SqlConnection noOpConnection = new     SqlConnection(connectionString))
                    {
                        noOpConnection.Open();

                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                            SqlCommand command =
                                new SqlCommand()
                                {
                                    CommandText = "ExceptionStoredProcedure",
                                    CommandType = System.Data.CommandType.StoredProcedure,
                                    Connection = connection
                                };
                            connection.Open();
                            command.ExecuteNonQuery();
                        }

                    } scope.Complete();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception caught: {0}", ex.Message);
            }
        }

    }
}

Message Printed By Client

Exception caught: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

Notes:

InnerException is null
If the transaction scope in either the C# console app or in the CLR stored procedure is removed then a DTC transaction is not created and the information for underlying error is returned .

Found work around thanks to links to articles provide by Bob Beauchemin (Partner, MVP) — AtTheMoment

AtTheMoment AtTheMoment · Accepted Answer · 2011-11-22T02:59:11

Found a workaround that uses SqlContext.Pipe.Send() thanks to links provided by Bob Beauchemin (Partner, MVP). See code below.

Notes: SqlClient appends "information messages" sent by SQL Server to SqlException.Message so client code that uses SqlClient doesn't need to change

CLR Stored Procedure

using System;
using System.Transactions;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ExceptionStoredProcedure()
    {
        using (TransactionScope scope = new TransactionScope())
        {
            try
            {
                throw new Exception(@"Test exception thrown from     ""ExceptionStoredProcedure""");
                scope.Complete();
            }
            catch (Exception ex)
            {
                if (Transaction.Current.TransactionInformation.DistributedIdentifier != Guid.Empty)
                    SqlContext.Pipe.Send(ex.Message);

                throw;
            }
        }
    }


};

Message Printed By Client

Exception caught: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Test exception thrown from "ExceptionStoredProcedure"

Possible Explanation

Based on article links found by Bob about DTC transaction abort and T-SQL TRY/CATCH I think the observed behaviour may unfold as follows:

Exception in transaction scope in CLR stored procedure causes a transaction abort vote to be sent to DTC.
DTC sends an abort request to all enlisted resource managers including SQL Server
SQL Server translates abort request into an attention signal to the SQL Server session in which the CLR stored procedure is executing
Attention signal changes how SQL Server CLR hosting code treats with unhandled CLR exception which in turn results in exception message not being included in result returned to client

Links to articles:

http://connect.microsoft.com/SQLServer/feedback/details/414969/uncatchable-error-when-distributed-transaction-is-aborted

http://msdn.microsoft.com/en-us/library/ms179296(SQL.90).aspx

How to get detailed error from CLR Stored Procedure called in DTC transaction from SSIS Package

1 Answers