WHERE clause using OPENQUERY with linked server

Question

I connect with MS SQL Server Express 2012 a Database via ODBC.

My query:

Select * from openquery(test,'SELECT * FROM Versuchsanlage_DB WHERE value =27')

But this query

 Select * from openquery(test,
   'SELECT * FROM Versuchsanlage_DB 
    WHERE identifier = AGENT.OBJECTS.Versuchsanlage.Variable_1_Byte')

OR

Select * from openquery(test,
   'SELECT * FROM Versuchsanlage_DB
    WHERE identifier = Variable_1_Byte')

doesn't work. Why?

Answer 1

You're searching for a string value in the identifier column. Strings must be enclosed in quotes. Since the select statement is being passed to OPENQUERY as a string, quotes inside that string must be escaped:

Select * from openquery(test,
   'SELECT * FROM Versuchsanlage_DB 
    WHERE identifier = ''AGENT.OBJECTS.Versuchsanlage.Variable_1_Byte''')

OR

Select * from openquery(test,
       "SELECT * FROM Versuchsanlage_DB 
        WHERE identifier = 'AGENT.OBJECTS.Versuchsanlage.Variable_1_Byte'")

These queries are functionally the same, one just uses all single-quotes while the other uses double-quotes. Pick whichever you think is easier to read.

Alternatively you could drop OPENQUERY and use EXECUTE...AT syntax for parameterization (this requires RPC to be enabled for the linked server):

EXECUTE('SELECT * FROM Versuchsanlage_DB WHERE identifier = ?',
        'AGENT.OBJECTS.Versuchsanlage.Variable_1_Byte') AT [test];