1

Can someone please explain to me what is wrong with the below expression. I believe that's I'm converting my date correctly using CONVERT 126 and that my quotes are escaped correctly using char 39 but I could be wrong.

T-SQL:

DECLARE @end2 DATETIME2 = GETDATE();

DECLARE @test2 nvarchar(200) = N'SELECT * FROM OPENQUERY(x, ' 
  + char(39) + 'SELECT OBJID FROM SALE WHERE MODIFIED >= ' 
  + CHAR(39) + CONVERT(nvarchar(24),@end2,126) 
  + char(39) + char(39) + ')';

PRINT @test2;

EXEC (@test2);

Print output:

select * from openquery(x, 'SELECT OBJID FROM SALE 
WHERE MODIFIED >= '2023-01-19T11:55:21.1233'')

Error:

Msg 102, Level 15, State 1
Incorrect syntax near '2023'.

Tried different formats, casting, etc. I can't use EXEC x..sys.sp_executesql because x is Firebird, not another SQL Server.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Guitoux
  • 23
  • 4

1 Answers1

3

You can escape the ' character with another one, i.e. ''. But you need to double escape it, i.e. your final string needs to have double single quotes in to be escaped in your dynamic SQL, which means a lot of escaping, i.e.

DECLARE @end2 DATETIME2
set @end2 = getdate()
declare @test2 nvarchar(200)
set @test2 = 'select * from openquery(x, ''SELECT OBJID FROM SALE WHERE MODIFIED >= '''''+convert(nvarchar(24),@end2,126)+''''''')'
print @test2
exec (@test2)

Which results in:

select * 
from openquery(x, 'SELECT OBJID FROM SALE WHERE MODIFIED >= ''2023-01-19T18:06:22.6033''')
GarethD
  • 68,045
  • 10
  • 83
  • 123
  • worked great. just need to understand these escapes better. – Guitoux Jan 19 '23 at 18:23
  • one thing i don't get is why is the date surrounded by two sets of quotes in the result? is that because it's nested basically? thx – Guitoux Jan 19 '23 at 20:39
  • You need the date surrounded by two sets of quotes in the query executed because you need one set in the result. i.e. what you want to execute on the remote server is: `SELECT OBJID FROM SALE MODIFIED >= '2023-01-19T18:06:22.6033' - If you had one quote, that just ends the string literal sent to the remote server, so you need two, the one you want in the final query, and the one to escape that – GarethD Jan 20 '23 at 09:29