执行远程存储过程插入本地表时遇分布式事务错误求助

我需要在SQL Server远程服务器上执行存储过程，并将结果写入本地表。

单独执行远程存储过程时可正常返回结果：

exec LIVE.DatabaseName.dbo.myStoredProcedure

但尝试将结果插入本地表时，触发以下错误：

insert into localTable (Field1, Field2, Field3) 
    exec LIVE.DatabaseName.dbo.myStoredProcedure

OLE DB 提供程序"MSOLEDBSQL"针对链接服务器"LIVE"返回消息"伙伴事务管理器已禁用其对远程/网络事务的支持。"

Msg 7391, Level 16, State 2, Line 29

无法执行操作，因为针对链接服务器"LIVE"的OLE DB提供程序"MSOLEDBSQL"无法启动分布式事务。

本地服务器已将RPC Out设置为True。

限制条件 #

必须确保远程LIVE服务器无法从本地开发服务器拉取数据，仅允许将生产数据拉取到开发环境。

补充提问 #

1. 仅在本地服务器设置链接服务器对象而远程服务器不设置是否有影响？
2. 如何不使用SSIS包实现需求？

一、绕过分布式事务错误的方案 #

INSERT...EXEC调用远程存储过程会触发分布式事务，而对方服务器禁用了该支持，可通过以下方式规避：

1. OPENQUERY + 临时表 #

利用OPENQUERY执行远程存储过程，将结果存入本地临时表后再插入目标表，此方式不会触发分布式事务：

-- 创建临时表匹配存储过程返回的字段结构
CREATE TABLE #TempResult (
    Field1 [对应数据类型],
    Field2 [对应数据类型],
    Field3 [对应数据类型]
)

-- 通过OPENQUERY拉取远程存储过程结果到临时表
INSERT INTO #TempResult
SELECT * FROM OPENQUERY(LIVE, 'EXEC DatabaseName.dbo.myStoredProcedure')

-- 将临时表数据插入本地目标表
INSERT INTO localTable (Field1, Field2, Field3)
SELECT Field1, Field2, Field3 FROM #TempResult

DROP TABLE #TempResult

注意：OPENQUERY要求本地链接服务器的RPC Out已开启（你的环境已满足），且远程存储过程的调用语句需符合远程服务器语法。

2. 表值函数包装（需远程服务器权限） #

若能在远程服务器上创建表值函数包装原存储过程的逻辑，可直接通过查询函数拉取数据：

-- 远程服务器上创建表值函数（需DDL权限）
CREATE FUNCTION dbo.myStoredProcedureWrapper()
RETURNS TABLE
AS
RETURN (
    -- 复制原存储过程的查询逻辑
    SELECT Field1, Field2, Field3 FROM [原存储过程的数据源]
)

-- 本地服务器直接查询并插入
INSERT INTO localTable (Field1, Field2, Field3)
SELECT Field1, Field2, Field3 FROM LIVE.DatabaseName.dbo.myStoredProcedureWrapper()

此方式同样不会触发分布式事务，且逻辑更简洁，但依赖远程服务器的操作权限。

二、补充提问解答 #

1. 仅本地设置链接服务器的影响 #

完全无影响。链接服务器是本地服务器的专属对象，仅用于本地发起对远程服务器的单向访问，远程服务器无需配置任何反向链接，正好符合你“仅允许本地拉取生产数据，远程无法访问本地”的需求。

2. 不使用SSIS的实现方式 #

除上述两种方案外，还可选择：

• bcp命令行 + BULK INSERT：先通过bcp远程执行存储过程并导出结果到本地文件，再用BULK INSERT导入本地表：

  bcp "EXEC LIVE.DatabaseName.dbo.myStoredProcedure" queryout "C:\temp\proc_result.txt" -S [本地SQL服务器名] -U [登录账号] -P [密码] -c -t,
  

  然后在SQL中执行：

  BULK INSERT localTable FROM 'C:\temp\proc_result.txt'
  WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
  

• CLR存储过程：编写.NET CLR代码实现远程调用与本地插入，但需开启SQL Server的CLR支持，复杂度较高，非必要不推荐。

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