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

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: 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!

set @date_string = '''08/01/2018'''

'SELECT ...'

2 Answers
2

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

OpenQuery

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

OpenQuery

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.

QuoteName

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:

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)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.