I'm running a rather simple query from a v16.0.0.2344 database against a MS SQL Server 2012 R2 database - and to my surprise I get an empty result set although it should return rows. The query contains a WHERE condition based on comparison with a datetime value, such as
and should return several rows with a date value in the 2nd have of this year. (It does not matter whether the datetime value is specified via a connection variable or as date literal). Using CIS_OPTION = 7 I can trace that the statement is passed to the MS SQL Server in full passthrough mode, such as
which looks ok. However, the statement when directly executed on the MS SQL Server will fail because of our DATEFORMAT (*) setting there (default for German locale = "dmy"), so the query returns a 242 conversion error - basically telling that the varchar value is out of range for a datetime. When I issue "SET DATEFORMAT ymd" on MS SQL Server, the query returns the correct result set. My questions:
(*): MS SQL Server's DATEFORMAT setting is comparable to SA's date_order option, but has no connection to SA's dateformat() function. |
Jack wrote:
Well, that seems to allow for a workaround at least: When I supply the comparison value as usual timestamp literal or as a timestamp variable and then convert the MS SQL Server datetime column to an international date format style (here 120 for ODBC canonical), the comparison is treated correctly: select t1.* from "MyDatabase".dbo."MyView" t1 where convert(varchar(30), t1."datField", 120) >= '2016-07-01 00:00:00.000' order by t1."datField"; I'd consider that a workaround as casting a datetime value won't lead to a sargable condition and might perform bad with a huge table. But in my case, it's sufficient. |
I have confirmed that: The database itself has just been copied to a test system, so all settings including the remote server definition should be equal. The behaviour differs:
Further testing revealed: The different SQL Native Client does not make a difference: I tested Machine 2 with the same version of SQL Native Client 11, and still don't get an error. However, the initial values listed by CIS_OPTION = 7 for a new connection are slightly different between both machines, with one different entry:
I have noticed that for Machine 2, the ODBC setting had the "AnsiNPW" set to "No" (I guess as a default). When I cleared that, I got the expected error message. Conclusion: So don't use MS SQL Server DSNs with "AnsiNPW=No" when you like to get ODBC errors... |
Just to add:
MS SQL Server also accepty the canonical ODBC date format, such as
but using that on the SA side
is a syntax error.
Re: where datField >= {d '2013-06-20'}
Where is it a syntax error? If you are using Interactive SQL, then you have to double-up the braces. Used in ODBC, it should be accepted.
Re: but it gets passed as '2016-07-01 00:00:00.000'
Did you try setting the timestamp_format string to use T?
Wow, doubled brackets - I wasn't aware of that. Is that a documented DBISQL feature? (And yes, I tested via DBISQL.)
I also adapted the timestamp_format, such as
However, that or the date specified in canonical ODBC syntax is still supplied to the MS SQL Server as '2013-06-20 00:00:00.000' and therefore leads to a out-of-range error.
I also tried to adapt the date_order on the SA side, such as
but that is again supplied to the MS SQL Server as '2013-06-20 00:00:00.000' and therefore leads to a out-of-range error, either.
(FWIW: In contrasts to my earlier tests I get an ODBC 22007 error in DBISQL, so at least the "silent empty result set problem" does not appear...)
So I'm out of my wits here.
(Of course the workaround is to ignore the date filter when importing the remote data, and apply that later in the local temporary table filled with all remote rows. That's alright in that case but not the desired behaviour...)
Haha, catch-22, apparently I once had known that, cf. that other FAQ - or to cite the docs:
Is datField a timestamp column? Perhaps if you cast it to char, then the formatting will be left untouched.
Yes, datField is a timestamp/datetime column. I'll try with a cast...
BTW, I had tested with a 16.0.0.2270 database server today, in contrast to 16.0.0.2344 last week (and possibly with a different SQL Server Native Client) - in case that does influence whether the date condition leads to an error or an empty result set...