Unfortunately, in the Microsoft SQL world, sometimes you have to deal with using OPENQUERY. This is difficult to use sometimes because you don't have as much flexibility with variables and parameters as you would using normal T-SQL. Below is an example of how you can use OPENQUERY and still utilize a variable or parameter.
DECLARE @variable VARCHAR(10)
DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000)
SET @variable = 'yourStringValue'
SET @sqlQuery = 'SELECT * FROM tableName WHERE strValue = ' + '''' + '''' + @variable + '''' + ''''
SET @finalQuery = 'SELECT * FROM OPENQUERY(LINKEDSERVER,' + '''' + @sqlQuery + '''' + ')'
--used for debugging
SELECT @finalQuery
EXEC(@finalQuery)
NOTE: It may be hard to see but the '''' is 4 single quotes
I recommend commenting out EXEC(@finalQuery) until you see @finalQuery correctly in the results. Running SELECT @finalQuery is very helpful because you can see what single quotes are missing if need be.
I also recommend getting your OPENQUERY statement to work correctly before trying to make it "dynamic" so to say using the above method.
Anyway, I found this very useful to use and hope that it can help some others out there.
48d1cf21-8f70-4d4f-83c6-288881c30204|3|4.7
Microsoft SQL
sql