CLR存储过程跨服务器取数时遭遇事务上下文占用错误求助

我来帮你分析并解决这个困扰你的问题：你在SERVER1上的SQL存储过程通过游标循环调用CLR函数ListCount，而这个CLR函数会连接到SERVER2获取数据，但每次循环都触发System.Data.SqlClient.SqlException: Transaction context in use by another session.错误。

问题根源 #

这个错误的核心原因是CLR函数在SERVER1的事务上下文内尝试建立外部连接时，事务传播机制引发了冲突。默认情况下，当SQL Server在一个活跃事务中调用CLR代码时，CLR创建的外部连接会自动尝试加入到当前事务中。但在游标循环的场景下，每次调用CLR函数都会重复绑定到同一个事务上下文，再加上跨服务器连接的特性，就容易触发这个“事务上下文被另一个会话占用”的错误。

可行的解决方案 #

根据你的业务场景，这里有几个实用的解决方向：

1. 禁用CLR连接的事务自动登记 #

最简单的办法是修改CLR代码里的连接字符串，添加Enlist=false参数，强制让这个连接不加入到SERVER1的事务中，让CLR的连接独立运行：

// 修改你的连接字符串，加上Enlist=false
string connectionStringToSERVER2 = "你的原有连接字符串;Enlist=false";
using (SqlConnection con = new SqlConnection(connectionStringToSERVER2))
{
    // 你的原有数据获取和处理逻辑
}

提示：这个方案适合你的CLR读取操作不需要和SERVER1的插入操作在同一个事务里的场景——毕竟你只是从SERVER2取数据处理后返回，不需要和SERVER1的插入事务强绑定，这应该是最适合你的情况。

2. 重构代码，去掉游标，批量处理 #

游标本身就是效率较低的操作，而且每次循环调用CLR函数都会建立一次连接，放大了事务冲突的概率。你可以改成批量处理的方式：

• 修改CLR函数，让它支持接收表值参数，一次性接收所有需要查询的条件，批量从SERVER2获取数据并返回完整结果集
• 修改SQL存储过程，把游标查询到的所有数据先存入一个表变量，然后一次性传给CLR函数，再批量插入到目标表

这样不仅能彻底解决事务冲突问题，还能大幅提升整个流程的性能，毕竟基于集合的操作才是SQL Server的强项。

3. 显式抑制CLR的事务上下文 #

如果你的业务逻辑必须保留SERVER1上的事务，但CLR的外部连接不需要参与其中，可以在CLR代码里用TransactionScope显式抑制事务：

[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "List_FillRow", TableDefinition = "Date datetime, Quantity int")]
public static IEnumerable ListCount(...)
{
    // 让CLR代码在无事务的上下文里执行
    using (var scope = new TransactionScope(TransactionScopeOption.Suppress))
    {
        using (SqlConnection con = new SqlConnection(connectionStringToSERVER2))
        {
            // 你的原有逻辑代码
        }
        scope.Complete();
        return list;
    }
}

这个方案适合你需要SERVER1的插入操作在事务中，但CLR的跨服务器读取不需要参与事务的场景。

额外小贴士 #

• 尽量避免使用游标，改用基于集合的操作，这不仅能解决这类事务问题，还能提升整体性能
• 如果确实需要用到分布式事务（不推荐），要确保SERVER1和SERVER2之间的MSDTC服务正常运行，但分布式事务会带来额外的性能开销和复杂度，能不用就不用

内容的提问来源于stack exchange，提问作者Alin I