Home Forums SQL Server 2008 SQL Server 2008 - General Table Variable Parameters - Odd design choices or am I just not finding the answer RE: Table Variable Parameters - Odd design choices or am I just not finding the answer

  • It is likely something to do with the User-defined Type not being present locally. Even though I have dbo.SampleDataType defined locally defined the same as the remote server I still cannot pass it across the Linked Server boundary using 4-part naming.

    This:

    declare @p1 dbo.SampleDataType

    insert into @p1 values(N'99',99)

    insert into @p1 values(N'98',NULL)

    insert into @p1 values(N'97',99)

    EXEC [SomeServer\SQL2008R2].test.dbo.SampleProcedure @Sample=@p1

    Yields this:

    Msg 7380, Level 16, State 1, Line 8

    Table-valued parameters are not allowed in remote calls between servers.

    This works OK though which should be expected as it parallels what .NET is doing by passing the table declaration and population within batch to be executed on the remote server:

    DECLARE @sql NVARCHAR(MAX) = '

    use test;

    declare @p1 dbo.SampleDataType

    insert into @p1 values(N''99'',99)

    insert into @p1 values(N''98'',NULL)

    insert into @p1 values(N''97'',99)

    EXEC dbo.SampleProcedure @Sample=@p1'

    EXEC(@sql) AT [SomeServer\SQL2008R2];

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato