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?
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];
