SQL - Calling SProc located in Server2 from Server1 using LinkedServer --> Need to Pass Table results from Server1 as Input parameter to this SProc

Question

Need help on below:

I need to execute a Stored procedure located in Server2 from Server1 like below:

-- Query on Server1
EXEC [LinkedServer2].DatahubDB.dbo.usp_Calcs @ClmsPaid Table, @Period = '2021'

For the first parameter @ClmsPaid, i need to pass results of a table with few columns in it which is available in Server1(This is where i am running the EXEC statement). Is it possible, if yes, how would i do it?

Answer 1

Sorry, I was a little lazy, and assumed that you might already have worked with XML data type. It is after all, a decently widely used feature.

The part:

 SELECT [MedNo], [ClmAmt], [fy_end], [fy_start]
 FROM  @xml.nodes(''/root/data'') AS T(c)

Should read:

SELECT T.c.value('@MedNo', 'varchar(50)') AS MedNo,
        T.c.value('@ClmAmt', 'numeric(11,2)') AS ClmAmt,
        T.c.value('@fy_end', 'date') AS fy_end,
        T.c.value('@fy_start', 'date') AS fy_start
FROM    @xml.nodes('/row') AS T(c)

Answer 2

You cannot pass table-valued parameters across linked servers. You will need to convert the data to XML or JSON, which you then shred on the remote server into table format again. Here is an outline:

DECLARE @xmltext nvarchar(MAX),
        @xml  xml,
        @sql nvarchar(MAX)

SET @xml = (SELECT * FROM @ClmsPaid FOR XML RAW)
SET @xmltext = cast(@xml as nvarchar(MAX))

SELECT @sql = '
   DECLARE @ClmsPaid TalbleType,
           @xml xml = cast(@xmltext AS xml)
   INSERT @ClmsPaid(...)
      SELECT ...
      FROM  @xml.nodes(''/root/data'') AS T(c)
   EXEC usp_Calcs @ClmsPaid Table, @Period = ''2021''
'
EXEC LinkedServer2.DatahubDB.sys.sp_executesql @sql, N'@xmltext nvarchar(MAX)', @xmltext

The XML document must be passed as nvarchar(MAX), since XML is yet another data type supported with linked servers.

Answer 3

Hi @Kumar ,

Execute a stored procedure of another server,
You can create a linked server, and then follow the steps below:

Code Snippet
EXEC <remote_server_name_with_instance>.<remote_database_name>.<remote_schema>.<remote_sp>

But as Erland said, table-valued parameters cannot be passed through the connection server.You can refer to Erland's method for passing table-valued parameters.

Echo

Answer 4

Hi @Erland Sommarskog ,

I tried below and got errors:

CREATE TYPE dbo.Test_ClmsPaid AS TABLE  
(  
	[MedNo] [varchar](50) NULL,  
	[ClmAmt] [numeric](11, 2) NULL,  
	[fy_end] [date] NULL,  
	[fy_start] [date] NULL  
)  
GO  
  
DECLARE @ClmsPaid dbo.Test_ClmsPaid  
  
INSERT INTO @ClmsPaid  
SELECT 12, 234.00, '2020-06-30', '2019-07-01' UNION  
SELECT 12, 234.00, '2019-09-30', '2018-10-01'   
  
-- SELECT * FROM @ClmsPaid  
  
DECLARE @xmltext	nvarchar(MAX),  
        @xml		xml,  
        @sql		nvarchar(MAX)  
SET @xml = (SELECT * FROM @ClmsPaid FOR XML RAW)  
SET @xmltext = cast(@xml as nvarchar(MAX))  
SELECT @sql = '  
    DECLARE @ClmsPaid dbo.Test_ClmsPaid,  
           @xml xml = cast(@xmltext AS xml)  
	INSERT @ClmsPaid ( [MedNo], [ClmAmt], [fy_end], [fy_start] )  
    SELECT [MedNo], [ClmAmt], [fy_end], [fy_start]  
    FROM  @xml.nodes(''/root/data'') AS T(c)  
      
	SELECT * FROM  @ClmsPaid  
'  
EXEC LinkedServer2.DatahubDB.sys.sp_executesql @sql, N'@xmltext nvarchar(MAX)', @xmltext  

Got below error:
Msg 207, Level 16, State 1, Line 14
Invalid column name 'MedNo'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'ClmAmt'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'fy_end'.
Msg 207, Level 16, State 1, Line 14
Invalid column name 'fy_start'.