How can I easily use TVPs with a linked server?

Question

The problem:

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.

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

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.

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

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 ?

Answer 1

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