I've been at this for a little longer then I'd like to admit.
From a SQL Server database, I'm trying to run a dynamic OPENQUERY on a Firebird database, but I'm getting in all kinds of trouble with the datetime condition. Now, I'm trying to use a parametrized approach to circumvent potential issues with the datetime format, but getting a token unknown error. Any advise is appreciated; full details below:
FYI, I know the query is redundant, but I'm just trying to get it to work.
t-sql:
DECLARE @days INT
DECLARE @start DATETIME
DECLARE @end DATETIME
DECLARE @sql nVARCHAR(MAX)
SET @days = 1
SET @end = GETDATE()
SET @start = DATEADD(DAY,-@days,@end)
SET @sql = N'(SELECT OBJID FROM SALE WHERE MODIFIED >= @test1)'
DECLARE @TEST nvarchar(max)
SET @TEST = N'SELECT * FROM OPENQUERY(x,'+CHAR(39)+'SELECT * FROM SALE WHERE OBJID IN '+@sql+CHAR(39)+')'
EXEC sp_executesql @TEST, N'@test1 datetime', @start
error:
LE DB provider "MSDASQL" for linked server "x" returned message "[ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 77
@".
I also tried the non-parametrized approach, but couldn't figure out what formats to put the datetime in.