MS SQL/OPENQUERY call to Informix -- too many single quotes? too few?

Question

I am trying to communicate with an external Informix datasource from an MS SQL 2016 database instance to issue a date bound query. To do this, I am doing the following:

--enter code here
declare @date_string varchar(10)
set @date_string = '08/01/2018'
-- this statement works
SELECT * FROM OPENQUERY ([ExternalLinkedServer], 'SELECT FIRST 10 * FROM informix.anydetailtable');
-- this does not work
SELECT * FROM OPENQUERY ([ExternalLinkedServer], 'SELECT FIRST 10 * FROM informix.anydetailtable WHERE eventdatetime between TODAY and date(' + @date_string + ')' );

EDITED (self-answer): After the reminder from David Dubois that variables are not accepted in OPENQUERY, here is the workaround approach:

enter code here
set @date_string = '''08/01/2018'''
declare @openquery nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = '[ExternalLinkedServer]'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT count(*) FROM informix.anydetailtable WHERE eventdatetime between TODAY and date(' + @date_string + ')'')'
print @openquery+@tsql
EXEC (@OPENQUERY+@TSQL)

The output of the print @openquery+@tsql looks like this:

SELECT * FROM OPENQUERY([ExternalLinkedServer],'SELECT count(*) FROM informix.anydetailtable WHERE eventdatetime between TODAY and date('08/01/2018')')

This looks correct, but obviously it is not because I get a syntax error near '08'. I have spent way more time on this than I should! Any and all advice will be greatly appreciated!

Try to enclose the string in single quotes, the other DBMS can "see": `set @date_string = '''08/01/2018'''` (or equivalent in the other string (`'SELECT ...'`)). — sticky bit, Aug 23 '18 at 22:36

David Dubois · Answer 1 · 2018-09-01T14:40:34.763

The second parameter to OpenQuery must be a string constant. You cannot put an expression here.

But you can build a string containing OpenQuery, and then execute that string.

declare @q nvarchar(max)

declare @x nvarchar(200)

set @x = 'The Criterion'

set @q = QuoteName ( @x, '''' )

set @q = 'select BookID from Books where Title=' + @q

set @q = QuoteName ( @q, '''' )

set @q = 'select * from OpenQuery ( OracleServer, ' + @q + ')'

select @q

exec sp_executesql @q

Counting quotes can be a source of frustration for a developer. It's easy to get it wrong.

I've demonstrated QuoteName in this example to show how useful it can be in dealing with embedded quotes. Calling QuoteName as shown will add quotes to the beginning and end of the string, but also double any quotes embedded within the string. This means that the developer doesn't need to work out how many quotes are needed. Let SQL do it for you.

create table Books ( BookID int, Title nvarchar(200) )

insert into Books ( BookID, Title ) values ( 381, 'Charlotte''s Web' )

declare @a nvarchar(200)
declare @b nvarchar(200)
declare @c nvarchar(200)
declare @d nvarchar(200)
declare @e nvarchar(200)

select top 1 @a = Title from Books

set @b = QuoteName ( @a, '''' )

select @a as [Title]

select @b as [Quoted Title]

set @c = 'select BookID from Books where Title=' + @b

set @d = QuoteName ( @c, '''' )

select @c as [Query]

select @d as [Quoted Query]

set @e = 'select * from OpenQuery ( OracleServer, ' + @d + ')'

select @e as [OpenQuery to be executed]

The results are:

Maybe using the 4 part query syntax would be easier: select * from linkedserver.stores7.informix.anydetailtable — jsagrera, Aug 24 '18 at 07:11

score 0 · Accepted Answer · answered Aug 28 '18 at 21:21

The approach @David Dubois offered is correct, however, fuzzy for anyone looking for a concrete answer. This is what actually works. Remember to pay special attention to the myriad of single quotes in order to pass a literal value, as in the case of the date.

    declare @openquery nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
    SET @LinkedServer = '[ExternalLinkedServer]'
    SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
    SET @TSQL = 'SELECT count(*) FROM informix.anydetailtable WHERE eventdatetime between ' + '''''2018-08-27 00:00:00''''' + '' + ' and TODAY' + ''')'
    -- print @openquery+@tsql   -- use this to examine your query, comment out when it is working
    EXEC (@OPENQUERY+@TSQL)

MS SQL/OPENQUERY call to Informix -- too many single quotes? too few?

2 Answers2