I'm running a rather simple query from a v18.104.22.1684 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.
(*): MS SQL Server's DATEFORMAT setting is comparable to SA's date_order option, but has no connection to SA's dateformat() function.
asked 25 Nov '16, 04:18
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.
So don't use MS SQL Server DSNs with "AnsiNPW=No" when you like to get ODBC errors...