SQL Server 自治事务（CLR 实现）解决方法

/*  * csc.exe /r:c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Transactions.dll /target:library AutoTranClass.cs */using System;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Transactions; public class AutoTran{    [SqlProcedure]    public static void ExecuteATStmt(SqlString sql, SqlString dbname, SqlInt16 iso)    {        // 创建不加入环境事务的事务作用域        using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Suppress))        {            using (SqlConnection conn = new SqlConnection("context connection=true"))            {                conn.Open();                // 获取过程调用者的安全上下文                SqlCommand cmd = conn.CreateCommand();                cmd.CommandText = "select suser_name()";                string loginame = (string)cmd.ExecuteScalar();                // 创建 LOOPBACK 连接（禁止使用连接池）                using (SqlConnection connAT =                   new SqlConnection("server=.;integrated security=true;database=master;pooling=false"))                {                    connAT.Open();                    connAT.ChangeDatabase((string)dbname);                    SqlCommand cmdAT = connAT.CreateCommand();                    // 定义自治事务的事务隔离等级                    string level;                    switch ((Int16)iso)                    {                        case 1:                            level = "read uncommitted";                            break;                        case 3:                            level = "repeatable read";                            break;                        case 4:                            level = "serializable";                            break;                        case 5:                            level = "snapshot";                            break;                        default:                            level = "read committed";                            break;                    }                    cmdAT.CommandText = "set transaction isolation level " + level;                    cmdAT.ExecuteNonQuery();                    // 将 CLR 存储过程运行的安全上下文切换到调用者的，                    //从而避免 CLR 过程运行在 SQL Server 服务进程的安全上下文中                    cmdAT.CommandText = "execute as login = '" + loginame + "' with no revert";                    cmdAT.ExecuteNonQuery();                    // 执行 SQL 语句                    cmdAT.CommandText = (string)sql;                    cmdAT.ExecuteNonQuery();                }            }            trans.Complete();        }    }}

use mastergoexec sp_configure 'show advanced options',1;goreconfiguregoexec sp_configure 'clr enabled',1;goreconfiguregoalter database master set trustworthy on;goif object_id('dbo.ExecuteATStmt') is not null drop procedure dbo.ExecuteATStmt;goif exists (select * from sys.assemblies where name='AutoTran') drop assembly AutoTran;gocreate assembly AutoTran authorization dbofrom 'C:\Devs\Projects\clrAutoTran\clrAutoTran\bin\Release\clrAutoTran.dll'with permission_set=external_access;gocreate procedure dbo.ExecuteATStmt  @stmt nvarchar(max), @dbname nvarchar(128)='master', @iso smallint=2as external name AutoTran.AutoTran.ExecuteATStmt;