使用用户定义的表类型变量参数执行远程过程 [英] Executing remote procedure with user-defined table type variable parameter

问题描述

我正在尝试通过链接服务器调用远程存储过程.问题是，所需的参数之一是用户定义的表类型.

I'm trying to call a remote stored procedure over a linked server. The problem is, one of the required parameters is a user-defined table types.

我似乎无法弄清楚如何从远程服务器上将本地变量声明为用户定义的表类型.

I can't seem to figure out how to declare a local variable as a user-defined table type from a remote server.

到目前为止，这是我正在尝试的方法，但是它不起作用:

This is what I'm trying so far, but it doesn't work:

DECLARE @tblVar [REMOTESERVER].REMOTEDB.dbo.user_defined_table_type

EXEC [REMOTESERVER].REMOTEDB.dbo.procedure_name (@param1 = @tblVar)

但是我遇到的错误是:

类型名称'REMOTESERVER.REMOTEDB.dbo'包含的前缀数量超过最大数量.最大值为1.

The type name 'REMOTESERVER.REMOTEDB.dbo' contains more than the maximum number of prefixes. The maximum is 1.

必须声明标量变量"@tblVar"

Must declare the scalar variable "@tblVar"

推荐答案

在进一步研究中，我发现表变量对于远程过程调用无效.

Upon further research I discovered that table variables are invalid for remote procedure calls.

相反，我所做的被称为EXEC [REMOTESERVER].[REMOTEDB]..sp_executesql，并声明并填充了表变量，并在其中全部调用了存储过程.

Instead, what I did was called EXEC [REMOTESERVER].[REMOTEDB]..sp_executesql and declared and populated my table variable and called the stored procedure all inside of that.

示例:

DECLARE @SQL nvarchar(4000)
SET @SQL = N'
DECLARE @tblVar dbo.user_defined_table_type
-- Code to populate table here
EXEC dbo.procedure_name (@param1 = @tblVar)
'
EXEC [REMOTESERVER].[REMOTEDB]..sp_executesql @stmt = @SQL

那解决了我的问题.希望这会在将来对其他人有所帮助.

And that solved my problem. Hopefully this will help someone else out in the future.

这篇关于使用用户定义的表类型变量参数执行远程过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！