0

I have a database in SQL Server 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 example I've got the stored proedure :

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 stored proedure test 42 I'm getting an error:

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 stored proedure to the linkedserver stored proedure which needs that int.

Any suggestions?

markalex
  • 8,623
  • 2
  • 7
  • 32
Dimitris
  • 133
  • 2
  • 13

1 Answers1

2
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.

Matt
  • 13,833
  • 2
  • 16
  • 28
  • I think that i need some type-casting tutorials...:P Thank you Matt , it's ok – Dimitris Nov 24 '16 at 19:16
  • @Dimitris you are welcome if you are all set please upvote and accept the answer so others know you have gotten what you need and reputation points are awarded. Thanks – Matt Nov 24 '16 at 19:20