One of the biggest challenges that I had to solve is how to call stored procedures over linked servers using TVPs. In an earlier article I explained how we could offload traffic to secondary readable servers by using linked servers. However, when we deal with stored procedures using TVPs, things get a little bit more complicated.

Table-valued parameters are not supported over linked servers. This is a constraint coming with SQL Server.

To solve it, we need to recreate the whole call, and run it over the linked server. This is done in the following manner.

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

As seen, the TVP will be scripted and recreated on the target server, this allowing us to run the same (or not) stored procedure on there.

As a warning, creating scripts from too big TVPs, can be costly in terms of CPU and network traffic.