none
problem while executing remote procedure with table-valued

    Question

  • I have a problem while executing remote procedure (stored on linked server) with table-valued parameter.

    Code on local server:

    USE [DB-test1] GO CREATE TYPE [dbo].[sync] AS TABLE( [LocalID] [int] NULL, [MainID] [int] NULL, [LocalCHECKSUM] [int] NULL, [MainCHECKSUM] [int] NULL, [LocalTime] [datetime] NULL, [MainTime] [datetime] NULL ) GO DECLARE @sync_test dbo.sync INSERT INTO @sync_test SELECT TOP 100 [LocalID] ,[MainID] ,[LocalCHECKSUM] ,[MainCHECKSUM] ,[LocalTime] ,[MainTime] FROM [dbo].[sync_patient] GO EXEC [RemServ].[DB_test2].[Zoltan].[NewData] @sync_test

    (100 row(s) affected)
    Msg 7380, Level 16, State 1, Line 18
    Table-valued parameters are not allowed in remote calls between servers.

    Code on remote server:

    >USE [DB_test2] GO CREATE TYPE [dbo].[sync] AS TABLE( [LocalID] [int] NULL, [MainID] [int] NULL, [LocalCHECKSUM] [int] NULL, [MainCHECKSUM] [int] NULL, [LocalTime] [datetime] NULL, [MainTime] [datetime] NULL ) GO CREATE PROCEDURE [dbo].[NewData] @sync dbo.sync READONLY AS BEGIN SELECT * FROM [dbo].[tbl_test] WHERE (ID<>-1) AND (ID NOT IN (select [MainID] from @sync)) END GO



    • Edited by dima kets Monday, February 04, 2013 3:52 PM
    • Moved by Papy Normand Monday, February 04, 2013 8:41 PM Not related to SQL Server Data Access
    Monday, February 04, 2013 3:47 PM

Answers

All replies