問題描述

問題:

我正在使用鏈接服務器從遠程服務器調用存儲過程.雖然這在大多數情況下都能正常工作，但我有許多使用表值參數 (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?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！