Executing remote procedure with user-defined table

问题:

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)

However the error I'm getting is:

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

Must declare the scalar variable "@tblVar"

回答1:

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

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.

Example:

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.