如何通过链接服务器轻松使用 TVP? [英] How can I easily use TVPs with a linked server?

问题描述

问题:

我正在使用链接服务器从远程服务器调用存储过程.虽然这在大多数情况下都能正常工作，但我有许多使用表值参数 (TVP) 的存储过程.MSSQL 无法调用用作参数 TVP 的远程 SP.

I am using a linked server to call stored procedures from a remote server. While this works fine most of the times, I have a number of stored procedures that use table-valued parameters (TVPs). MSSQL cannot call remote SPs that use as parameters TVPs.

解决方法是在远程 sql 上执行 sql 并在那里构建 tvps.这再次工作正常.

The workaround is to execute sql on the remote sql and build there the tvps. Again this works fine.

问题是我必须编写字符串才能调用 SP.如果我的 TVP 很少，这或多或少是容易的，但我的 SP 有很多 TVP.

The problem is that I have to compose the string to call the SP. In the case when I have few TVPs, this is more or less easy, but I have SPs with a lot of TVPs.

现在，在分析存储过程调用时，在 TVP 参数存储过程的情况下，从 .NET 到 sql 的调用如下所示:

Now, when profiling a Stored Procedure call, the call from .NET to sql in case of a TVP parameter stored procedure looks like:

declare @p1 <type>
insert into @p1 values(...)
insert into @p1 values(...)

...

exec myProc @p1

我想做的是在我的服务器上做一个包装器(与 sp 远程相同)并在使用 exec sql 调用远程服务器内.

What I want to do is a wrapper on my server (identical with the sp remote) and within call the remote server with exec sql.

现在有人如何(如果可以)从存储过程访问此查询?访问它自己的分析器，如查询，以便我可以远程发送?

Does anyone now how can I (if I can) access this query from a stored procedure? Access it's own profiler like query so that I can just send it remote ?

推荐答案

好的，所以基本上解决方案是这样的(那种自动化了一半的问题):

Ok, so basically the solution is this (that kind of automates half of the problem) :

declare @tvpVal_string nvarchar(max) = 'declare @tvpVal myTVPType;'
set tvpVal_string += isnull(stuff((select ';insert into @tvpVal values('+...your values...+')' as [text()] from @tvpVal from xml path('')),1,1,'')+';','');

declare @sql nvarchar(max) = tvpVal_string + 
'exec myProc @tvpVal=@tvpVal,
             @OtherVal=@OtherVal'

exec [REMOTESRV].DB..sp_executesql @sql,'@OtherVal type',@OtherVal

这篇关于如何通过链接服务器轻松使用 TVP?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！