从链接服务器上执行的存储过程返回nvarchar(max) [英] Return nvarchar(max) from stored procedure executed on linked server
问题描述
我的存储过程在其选择输出中返回nvarchar(max):
I have stored procedure that returns nvarchar(max) in it's select output:
创建过程[ _TEST]
as
begin
声明@Data nvarchar(max )= replicate('x',50000)
选择@Data作为[数据]
end
create procedure [_TEST]
as
begin
declare @Data nvarchar(max) = replicate('x', 50000)
select @Data as [Data]
end
我需要在链接服务器上执行此sp并将此nvarchar(max)值读取到@variable nvarchar(max)。
I need to execute this sp over linked server and read this nvarchar(max) value to @variable nvarchar(max).
有没有人知道怎么做?
我试过这个:
声明@Table表([Data] nvarchar(max))
declare @Table table ([Data] nvarchar(max))
插入@Table([Data])
exec sp_executesql @sql_source,@ pars_source
insert into @Table ([Data])
exec sp_executesql @sql_source, @pars_source
但它抱怨MSDTC
谢谢
推荐答案
我们需要准确的错误信息。
We need the exact error message.
我设置了一个链接SQL1到SQL2上的ed服务器。
I setup a linked server on SQL1 to SQL2.
在SQL2上
create procedure [TESTbb]
as
begin
declare @Data nvarchar(max) = replicate(cast('x' as nvarchar(max)), 50000)
select @Data as [Data]
end
在SQL1上
create table tablebb([Data] nvarchar(max))
insert into tablebb (Data)
exec orlhzn360dv03.bank04.dbo.TESTbb
生成错误消息 -
OLE DB提供程序" SQLNCLI10"对于链接服务器"orlhzn360dv03"返回消息"合作伙伴事务管理器已禁用其对远程/网络事务的支持。"
OLE DB provider "SQLNCLI10" for linked server "orlhzn360dv03" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
消息7391,级别16,状态2,行3 $
由于OLE DB提供程序"SQLNCLI10",因此无法执行该操作。对于链接服务器"orlhzn360dv03"无法开始分发交易。
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "orlhzn360dv03" was unable to begin a distributed transaction.
要更正此特定错误
https://blog.sqlauthority.com/2016/01/12/sql-server-fix-msg-7395-level-16-state-2-unable -to-start-a-nested-transaction-for-ole-db-provider /
https://blog.sqlauthority.com/2016/01/12/sql-server-fix-msg-7395-level-16-state-2-unable-to-start-a-nested-transaction-for-ole-db-provider/
这篇关于从链接服务器上执行的存储过程返回nvarchar(max)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!