Как исправить ошибку System.Data.SqlClient.SqlException: Transaction context in use by another session?

Question

[strongmonkey]

задача:

на стороне sql открыта транзакция внутри которой что то происходит.
внутри этой транзакции нужно вызвать clr процедуру на C# которая что то куда то запишет
и в случае отката транзакции, действия которые были выполнены в процессе работы clr функции
не должны откатится.

для теста накидал метод на C#

namespace Future.SQLCLR
{
    public class Exception
    {
        public static string ConnectionString
        {
            get
            {
                //string ret = "context connection = true";//
                string ret = "Data Source = server; Initial Catalog = database; Integrated Security = True";
                return ret;
            }
        }


        [SqlFunction()]
        public static void TestInsert(SqlString inputParam)
        {
            using (var cn = new SqlConnection(ConnectionString))
            {
                try
                {
                    cn.ConnectionString = "Enlist=false";

                    cn.Open();
                    
                    var cmd = new SqlCommand("insert into table(field)" +
											 "select field from table_1", cn);

                    cmd.ExecuteNonQuery();
                }
                catch (System.Exception)
                {

                    throw;
                }
                finally
                {
                    cn.Close();
                }
            }
        }
    }
.......
}

CREATE PROCEDURE [audit].[TestInsert]
(
	@msg   nvarchar(2048)
)
AS EXTERNAL NAME CLR.[SQLCLR.Exception].TestInsert

begin tran

exec [TestInsert] 'msg'

rollback

получаю ошибку System.Data.SqlClient.SqlException: Transaction context in use by another session.

Answer 1

[Dmitry Pavlov]

Смущает описание хранимой процедуры - это ее часть какая-то? Или что это такое?

CREATE PROCEDURE [dbo].[MyPROC]
(
  @Param1 nvarchar(2048) out,
  @Param2 int out,
  @Param3 int out,
  @Param4 int
)
AS EXTERNAL NAME DWH_CLR.[Future.SQLCLR.Exception].HandleException

Comments

[strongmonkey]

Это текст хранимой процедуры. А что вас смущает?

[Dmitry Pavlov]

strongmonkey: не пойму где тело процедуры - что она делает?

[strongmonkey]

Dmitry Pavlov: тело процедуры

public static void HandleException(out SqlString aParam1, out SqlInt32 aParam2, out SqlInt32 aParam3,
            SqlInt32 aParam4)
        {
            using (var connect = new SqlConnection(ConnectionString))
            {
                try
                {
                    connect.Open();

                    using (var command = new SqlCommand("[dbo].[MyPROC]", connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;

                        Dictionary<string, SqlParameter> dictionary = new Dictionary<string, SqlParameter>();

                        dictionary.Add("ErrorMessage", new SqlParameter("@Param1", SqlDbType.NVarChar, 2048));
                        dictionary.Add("ErrorNumber", new SqlParameter("@Param2", SqlDbType.Int));
                        dictionary.Add("ErrorState", new SqlParameter("@Param3", SqlDbType.Int));
                        dictionary.Add("ErrorProcID", new SqlParameter("@Param4", SqlDbType.Int));

                        foreach (var kvp in dictionary)
                        {
                            command.Parameters.Add(kvp.Value);

                            if (kvp.Key != "ErrorProcID") 
                            {
                                kvp.Value.Direction = ParameterDirection.Output;
                            }
                            
                        }

                        dictionary["Param4"].Value = 0;

                        command.ExecuteNonQuery();

                        aParam1= (SqlString) dictionary["Param1"].Value;
                        aParam2= (SqlInt32) dictionary["Param2 "].Value;
                        aParam3= (SqlInt32) dictionary["Param3 "].Value;
                    }

                }
                finally
                {
                    connect.Close();
                }
            }
        }

[Dmitry Pavlov]

strongmonkey: ах ну да - это ж CLR хранимка. Тогда смущает отсутствие атрибута [Microsoft.SqlServer.Server.SqlProcedure] и имена параметров в хранимке и в методе разные.