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

Answer 1

Hello,

I am moving your thread towards the Transact-SQL Forum which seems ( according to me ) a better forum.

I hope you will find a quick and full response in this new forum.

Anyway, please, could you tell us what you mean when you wrote " executing remote procedure (stored on linked server) with table-valued parameter" ?

Please, could you tell us what is the provider you are using to create your linked server ?

2 possibly useful links :

http://msdn.microsoft.com/en-us/library/bb675163(v=VS.110).aspx  ( even if the list of limitations is not clear )

http://msdn.microsoft.com/en-us/library/bb510489(SQL.100).aspx    ( see the part Scope )

I can't help us more deeply as i am not a specialist of linked servers and table-valued parameters

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 2

Hi

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

The error message is very clear.  Table Valued Parameters are NOT  supported across servers. i think its not even supported across databases on the same server

Have a look at this link for some alternatives

http://www.sommarskog.se/arrays-in-sql-2008.html#CLR

---

Regards
Satheesh

Answer 3

Hi

You view the similar post on :

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/b3f1a1d2-6016-4273-8f1c-23d2a4cf071a

Also read the article below:

http://stackoverflow.com/questions/10982360/workaround-for-calling-table-valued-function-remotely-in-sql-server-has-even-mor

Hope your problem will be solved :) .

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/