{"id":59,"date":"2013-07-02T14:25:00","date_gmt":"2013-07-02T19:25:00","guid":{"rendered":"https:\/\/www.chrystalsander.com\/blog\/?p=59"},"modified":"2023-07-26T12:33:48","modified_gmt":"2023-07-26T17:33:48","slug":"sql-server-using-quotes-in-linked-server-queries","status":"publish","type":"post","link":"https:\/\/www.chrystalsander.com\/blog\/?p=59","title":{"rendered":"SQL Server : Using Quotes In Linked Server Queries"},"content":{"rendered":"
\n
\n
\nDoing an OpenQuery across a linked server can get ugly very fast and it is often best to avoid them.\n<\/div>\n
\nThe basic syntax of an OpenQuery is :<\/div>\n
\n<\/div>\n
\nSELECT<\/span> *<\/span> FROM<\/span> OPENQUERY<\/span>(<\/span>YourLinkedServerName,<\/span> ‘SELECT FIELD1, FIELD2 FROM YOURTABLE’<\/span>)<\/span><\/span><\/div>\n
\n<\/div>\n
\nFrom a coding perspective, this gets ugly if you have to incorporate string parameters; thus, utilizing many quotes.  For Example:<\/div>\n
\n<\/div>\n
\nSELECT<\/span> *<\/span> FROM<\/span> OPENQUERY<\/span>(<\/span>YourLinkedServerName,<\/span> ‘SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = ”ID105” ‘<\/span>)<\/o:p><\/span><\/span><\/div>\n
\n<\/div>\n
\nMicrosoft made this easier with the built-in stored procedure that allows you to avoid multi-layered quotes:  sp_executesql<\/span><\/div>\n
\n<\/div>\n
\nThe above can become:<\/o:p><\/div>\n
\nDECLARE<\/span> @PARAM char<\/span>(<\/span>5)<\/o:p><\/span><\/span><\/div>\n
\nSELECT<\/span>  @PARAM =<\/span> ‘CA105’<\/o:p><\/span><\/span><\/div>\n
\nEXEC<\/span> YourLinkedServer.<\/span>master<\/span>.<\/span>dbo.<\/span>sp_executesql<\/o:p><\/span><\/span><\/div>\n
\nN’SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = @p1′<\/span>,<\/o:p><\/span><\/div>\n
\nN’@p1 char(5)’<\/span>,<\/o:p><\/span><\/div>\n
\n@PARAM<\/span><\/div>\n
\n<\/div>\n
\nBut, what if your WHERE clause becomes:<\/div>\n
\n<\/div>\n
\nWHERE <\/span>FIELD2 in (”ID105”,”ID107”,”ID109”,”ID112”,”ID116”,<\/o:p><\/span><\/div>\n
\n”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”,<\/o:p><\/span><\/div>\n
\n”ID413”)<\/span><\/div>\n
\n<\/div>\n
\nThere are multiple ways to go about this but here is one cheap method for dealing with the quotes:<\/o:p><\/div>\n
\nDECLARE <\/span>@TSQL NVARCHAR(2000);<\/o:p><\/span><\/span><\/div>\n
\nDECLARE <\/span>@CODES NVARCHAR(500);<\/o:p><\/span><\/span><\/div>\n
\nDECLARE<\/span> @OPENQUERY NVARCHAR<\/span>(<\/span>100);<\/o:p><\/span><\/span><\/div>\n
\nDECLARE<\/span> @LINKED_SQL NVARCHAR<\/span>(<\/span>2700)<\/span><\/o:p><\/span><\/span><\/div>\n
\n<\/div>\n
\nSET<\/span> @CODES = ”’ID105”,”ID107”,”ID109”,”ID112”,”ID116”,<\/o:p><\/span><\/span><\/span><\/div>\n
\n”ID119”,”ID400”,”ID404”,”ID101”,”ID102”,”ID115”,”ID106”,”ID111”,”ID117”,”ID412”,”654Z ”,”ID103”,”ID121”,”ID401”,”ID403”,<\/span>”ID407”,”ID108”,”ID113”,”ID114”,”ID120”,”ID123”,”ID405”,”ID409”,”ID100”,”ID110”,”ID124”,”ID408”,”ID104”,”ID118”,”ID406”,<\/o:p><\/span><\/div>\n
\n”ID413”’<\/span>;<\/span><\/div>\n
\n<\/div>\n
\nSELECT<\/span> @TSQL =<\/span> ‘SELECT \u2026 WHERE FIELD2 IN (\u2018<\/span>+<\/span> @CODES +<\/span> ‘)’<\/span>   <\/span>\n<\/div>\n
\n— CLEAN UP THE QUOTES FOR THE OPENQUERY<\/o:p><\/span><\/div>\n
\n—————————————————–                  <\/o:p><\/span><\/div>\n
\nSET<\/span> @TSQL =<\/span> REPLACE<\/span>(<\/span>@TSQL,<\/span> ””<\/span>,<\/span>”””<\/span>)<\/span>  ;<\/o:p><\/span><\/span><\/div>\n
\nSET<\/span> @TSQL =<\/span> @TSQL +<\/span> ”’)’<\/span>;<\/o:p><\/span><\/span><\/div>\n
\nSET<\/span> @OPENQUERY =<\/span> ‘SELECT * FROM OPENQUERY(GHDB, ”’<\/span>;<\/o:p><\/span><\/span><\/div>\n
\nSET<\/span> @LINKED_SQL =<\/span> <\/span>(<\/span>@OPENQUERY+<\/span>@TSQL)<\/o:p><\/span><\/span><\/div>\n
\n<\/div>\n
\nEXEC<\/span> sp_executesql<\/span> <\/span>@LINKED_SQL<\/span><\/o:p><\/div>\n
\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

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.  … Continue reading “SQL Server : Using Quotes In Linked Server Queries”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[50],"_links":{"self":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/59"}],"collection":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=59"}],"version-history":[{"count":1,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/59\/revisions"}],"predecessor-version":[{"id":174,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/59\/revisions\/174"}],"wp:attachment":[{"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=59"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=59"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chrystalsander.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=59"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}