Problem with remote data access to MS SQL Server and different date order setting

[VolkerBarth]

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

select * from MyRemoteMsView
where datField >= '2016-07-01';

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

select t1.* from "MyDatabase".dbo."MyView" t1
where t1."datField" >= '2016-07-01 00:00:00.000'

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:

• Why is the conversion error on the MS SQL Server side not returned to SQL Anywhere? (An error message would have helped to find out that something is wrong, much better than an empty result set)?
• How can I force DATEFORMAT = ymd via Remote Data Access? I tried to run "FORWARD TO MySqlServer 'SET DATEFORMAT = ymd', and this was successfully, but apparently had no impact on the next remote query.
• Shouldn't the remote data access layer itself handle different date orders accordingly - or use the ISO8601 representation (here: '2016-07-01T00:00:00.000') which should be recognized independent of the date order setting? (I tried to specify the date literal in that ISO format but it gets passed as '2016-07-01 00:00:00.000' and is therefore rejected, either.)

(*): MS SQL Server's DATEFORMAT setting is comparable to SA's date_order option, but has no connection to SA's dateformat() function.

[VolkerBarth]

Just to add:

MS SQL Server also accepty the canonical ODBC date format, such as

[...] where t1."datField" >= {d '2016-07-01'}

but using that on the SA side

[...] where datField >= {d '2013-06-20'}

is a syntax error.

[jack_schueler]

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?

[VolkerBarth]

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

set temporary option timestamp_format = 'YYYY-MM-DDTHH:NN:SS.SSSSSS';
create variable datField datetime = '2013-06-20';
select datField; -- returns '2013-06-20T00:00:00.000000'

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

set temporary option date_order = 'dmy';
set temporary option timestamp_format = 'DD.MM.YYYY HH:NN:SS.SSSSSS';
select datField; -- returns '20.06.2013 00:00:00.000000'

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...)

[VolkerBarth]

Wow, doubled brackets - I wasn't aware of that. Is that a documented DBISQL feature? (And yes, I tested via DBISQL.)

Haha, catch-22, apparently I once had known that, cf. that other FAQ - or to cite the docs:

In Interactive SQL, the braces must be doubled. There must not be a space between successive braces: "{{" is acceptable, but "{ {" is not. As well, you cannot use newline characters in the statement. The escape syntax cannot be used in stored procedures because they are not parsed by Interactive SQL.

[jack_schueler]

Is datField a timestamp column? Perhaps if you cast it to char, then the formatting will be left untouched.

[VolkerBarth]

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...

[VolkerBarth · Accepted solution]

Jack wrote:

Is datField a timestamp column? Perhaps if you cast it to char, then the formatting will be left untouched.

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.

[VolkerBarth]

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...

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:

• Machine 1: Win 2012 R2, SA 16.0.0.2270, MS SQL Server as remote server via SQL Native Client 11: I get a -660 error code.
• Machine 2: Win 7, SA 16.0.0.2344, MS SQL Server as remote server via SQL Native Client 10: Just an empty result set, no error message.

---

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:

• Machine 1: Concatenation null behavior = SQL_CB_NULL
• Machine 2: Concatenation null behavior = SQL_CB_NON_NULL

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...