Stored procedure to Linked server with parameters - Error

Question

I'm trying to create stored procedure to Linked server, which input parameter @ServerName is the name of Linked Server i use. In this procedure I also Declare parameter which value I want to get from Dynamic SQL Query and line.

CREATE PROC sp_Version @ServerName varchar(30)
as
Declare @Ver varchar(10)
exec ('select @Ver from openquery(' + @ServerName + ', ''SELECT SUBSTRING (@@VERSION, 22, 7) = @Ver''')

When I execute my sp i get an error saying:

"Must declare the scalar variable "@Ver"."

Could you please help me?

Answer 1

I have come across this situation a couple of times. Try this:

CREATE PROC sp_Version @ServerName varchar(30)
as
Declare @Ver varchar(10)

DECLARE @SqlCommand nvarchar(MAX)

SET @SqlCommand = 'SELECT @Ver2 = SUBSTRING (@@VERSION, 22, 7) '

DECLARE @sp_executesql VARCHAR(100)
SET @sp_executesql = @ServerName + '.master.sys.sp_executesql'
EXEC @sp_executesql @SqlCommand, N'@Ver2 nvarchar(10) out', @Ver out
SELECT @Ver