How can I use a variable inside OPENQUERY

Question

I am getting an error running the code below. I believe it as to do with the number of single quotes used. How can I set a variable inside OPENQUERY command?

@declare @myStr varchar(6)
set @myStr = 'Test1'

SELECT *    
FROM OPENQUERY("192.168.1.1",'SET FMTONLY OFF; EXEC spNewTest @Param1 = ''+ @myStr +''')

Click to see the error message

Regards, Elio Fernandes

@ElioFernandes, according to https://msdn.microsoft.com/en-us/library/ms188427.aspx, the query string cannot contain variables. You'll need to build the entire `SELECT...OPENQUERY` statement and execute the statement string with `EXECUTE` or `sp_executesql`. — Dan Guzman, Sep 16 '16 at 15:04

score 1 · Answer 1 · answered Sep 16 '16 at 11:44

If you want to create a single quote within a string you have to put in two single quotes. For example: '''this is a string''' would be a string containing the following: 'this is a string'

So for your problem, you have to change your code to this:

@declare @myStr varchar(6)
set @myStr = 'Test1'

SELECT *    
FROM OPENQUERY("192.168.1.1",'SET FMTONLY OFF; EXEC spNewTest @Param1 = '''+ @myStr +'''')

score 1 · Accepted Answer · answered Sep 17 '16 at 21:26

I just found the answer for my question, so I thought sharing it with you.

DECLARE @QUERY VARCHAR(MAX)
DECLARE @TSQL VARCHAR(100)
DECLARE @SP VARCHAR(50)

DECLARE @PARAMETERS VARCHAR(MAX)
DECLARE @PARAM1 VARCHAR(50)
DECLARE @PARAM2 VARCHAR(50)

SET @TSQL = N'SELECT * FROM OPENQUERY([192.168.1.1], ''SET FMTONLY OFF; '
SET @SP   = 'EXEC spNewTest '

SET @PARAM1 = '@Type='''+ QUOTENAME('Test','''') + ''''
SET @PARAM2 = '@Year='''+ QUOTENAME('2016','''') + ''''
SET @PARAMETERS = @PARAM1 + ', ' + @PARAM2

SET @QUERY = @TSQL + @SP + @PARAMETERS + ''')'

EXECUTE (@QUERY)

Thanks

How can I use a variable inside OPENQUERY

2 Answers2