Dimitris Dimitris - 8 days ago 7
SQL Question

Execute sp in openquery with parameters

I have a db on SQL Management Studio (2014) and i have a linked server.
i want to run a sp which runs inside her a stored procedure
which belongs to the linked server.

For exaple i've got the sp:

PROCEDURE test (@myint int) AS
DECLARE @Query VARCHAR(2000)
SET @Query = 'INSERT INTO mytable SELECT * FROM OPENQUERY([mylinkedserver], ''EXEC [linkedserverdb].[dbo].[linkedserversp]
'+ @myint + ' '')'

EXEC(@Query)


When i'm running the sp
test 42
i'm taking back

Conversion failed when converting the varchar value 'INSERT INTO mytable SELECT * FROM OPENQUERY([mylinkedserver], 'EXEC [linkedserverdb].[dbo].[linkedserversp]
' to data type int.


Notice that i'm trying to pass the variable myint from my sp to the linkedserversp which needs that int.

Any suggestions?

Answer
PROCEDURE test (@myint  int) AS 
DECLARE @Query VARCHAR(2000)
SET @Query = 'INSERT INTO mytable SELECT * FROM OPENQUERY([mylinkedserver], ''EXEC  [linkedserverdb].[dbo].[linkedserversp]  
'+ CAST(@myint AS VARCHAR(10)) + ''')'

EXEC(@Query)

You are trying to concatenate a INT @myint with a string 'exec...' you have to cast @myint to a compatible character type e.g. VARCHAR.

Comments