Doing an OpenQuery across a linked server can get ugly very fast and it is often best to avoid them.
The basic syntax of an OpenQuery is :
SELECT * FROM OPENQUERY(YourLinkedServerName, ‘SELECT FIELD1, FIELD2 FROM YOURTABLE’)
From a coding perspective, this gets ugly if you have to incorporate string parameters; thus, utilizing many quotes. For Example:
SELECT * FROM OPENQUERY(YourLinkedServerName, ‘SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = ”ID105” ‘)
Microsoft made this easier with the built-in stored procedure that allows you to avoid multi-layered quotes: sp_executesql
The above can become:
DECLARE @PARAM char(5)
SELECT @PARAM = ‘CA105’
EXEC YourLinkedServer.master.dbo.sp_executesql
N’SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = @p1′,
N’@p1 char(5)’,
@PARAM
But, what if your WHERE clause becomes:
WHERE FIELD2 in (”ID105”,”ID107”,”ID109”,”ID112”,”ID116”,
”ID119”,”ID400”,”ID404”,”ID101”,”ID102”,”ID115”,”ID106”,”ID111”,”ID117”,”ID412”,”654Z”,”ID103”,”ID121”,”ID401”,”ID403”,”ID407”,”ID108”,”ID113”,”ID114”,”ID120”,”ID123”,”ID405”,”ID409”,”ID100”,”ID110”,”ID124”,”ID408”,”ID104”,”ID118”,”ID406”,
”ID413”)
There are multiple ways to go about this but here is one cheap method for dealing with the quotes:
DECLARE @TSQL NVARCHAR(2000);
DECLARE @CODES NVARCHAR(500);
DECLARE @OPENQUERY NVARCHAR(100);
DECLARE @LINKED_SQL NVARCHAR(2700)
SET @CODES = ”’ID105”,”ID107”,”ID109”,”ID112”,”ID116”,
”ID119”,”ID400”,”ID404”,”ID101”,”ID102”,”ID115”,”ID106”,”ID111”,”ID117”,”ID412”,”654Z ”,”ID103”,”ID121”,”ID401”,”ID403”,”ID407”,”ID108”,”ID113”,”ID114”,”ID120”,”ID123”,”ID405”,”ID409”,”ID100”,”ID110”,”ID124”,”ID408”,”ID104”,”ID118”,”ID406”,
”ID413”’;
SELECT @TSQL = ‘SELECT … WHERE FIELD2 IN (‘+ @CODES + ‘)’
— CLEAN UP THE QUOTES FOR THE OPENQUERY
—————————————————–
SET @TSQL = REPLACE(@TSQL, ””,”””) ;
SET @TSQL = @TSQL + ”’)’;
SET @OPENQUERY = ‘SELECT * FROM OPENQUERY(GHDB, ”’;
SET @LINKED_SQL = (@OPENQUERY+@TSQL)
EXEC sp_executesql @LINKED_SQL