通过链接服务器调用跨服务器存储过程获取XML输出参数的方法咨询

核心问题说明 #

SQL Server通过链接服务器调用远程存储过程时，XML类型的输出参数直接接收会触发类型兼容报错，因为分布式查询对XML类型的跨实例传输支持有局限，建议通过文本中转的方式实现兼容，同时可以正常获取存储过程返回的数据集和其他类型输出参数。

前置配置要求 #

首先确认链接服务器已开启RPC OUT权限，否则无法读取远程存储过程的输出参数，执行以下配置即可（仅需配置一次）：

sp_serveroption '你的链接服务器名称', 'RPC OUT', 'TRUE';

具体实现步骤 #

方案：NVARCHAR(MAX)中转法（兼容性最高，无数据丢失风险） #

无需修改部署端的存储过程，仅需调整调用端的变量类型即可：

1. 调用端先声明NVARCHAR(MAX)类型的临时变量接收远程存储过程的XML输出参数
2. 收到返回值后再转为XML类型使用
3. 若需同时接收存储过程返回的数据集，可通过INSERT INTO ... EXEC的方式存储到本地表/临时表

完整代码示例 #

调用端（服务器1）代码

-- 1. 声明变量
DECLARE 
    @para1 输入参数1类型 = '参数1值',
    @para2 输入参数2类型 = '参数2值',
    @para3 VARCHAR(1000), -- 接收varchar类型输出参数
    @temp_xml NVARCHAR(MAX), -- 中转XML输出的临时变量
    @para4 XML -- 最终得到的XML类型输出参数

-- 2. 预创建临时表接收存储过程返回的数据集，字段结构和存储过程返回的结果集完全一致
CREATE TABLE #temp_result (
    字段1 类型,
    字段2 类型,
    -- 其他字段按需补充
)

-- 3. 调用远程存储过程，同时获取结果集和输出参数
INSERT INTO #temp_result
EXEC [你的链接服务器名称].[部署存储过程的数据库名].[架构名（一般是dbo）].[sp_name] 
    @para1 = @para1,
    @para2 = @para2,
    @para3 = @para3 OUTPUT,
    @para4 = @temp_xml OUTPUT

-- 4. 将中转的文本转为XML类型
SET @para4 = CAST(@temp_xml AS XML)

-- 验证结果
SELECT * FROM #temp_result -- 返回的数据集
SELECT @para3 AS varchar输出参数, @para4 AS XML输出参数

部署端（服务器2）存储过程无需修改，保持原有逻辑即可

CREATE PROCEDURE sp_name 
    @para1 类型,
    @para2 类型,
    @para3 VARCHAR(1000) OUTPUT,
    @para4 XML OUTPUT
AS 
BEGIN 
    SET @para3 = 'varchar类型返回值'
    SET @para4 = (SELECT ... FROM #table_name 
                 FOR XML PATH('RowData'),TYPE, ROOT('Output') ) 

    SELECT * FROM #tablename 
END

注意事项 #

• 中转用的NVARCHAR(MAX)最大支持2GB长度，可覆盖绝大多数XML返回场景
• 如果存储过程返回的结果集结构不固定，无法提前创建临时表，可改用OPENROWSET方式查询，但该方式无法同时获取输出参数，优先推荐上述中转方案
• 确保链接服务器的登录账号有存储过程的执行权限和对应表的查询权限

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