SQL native client data type compatibility - incompatible with SQLOLEDB

Question

We have ran into a problem with our legacy application when switching to use SQL Native Client (SQLNCLI) as the provider for ADO.

Our original connection string was:

Provider=SQLOLEDB; Server=host; Database=bob; Integrated Security=SSPI;

we changed this to:

Provider=SQLNCLI11; Server=host; Database=bob; Integrated Security=SSPI; DataTypeCompatibility=80;

What we've found is that when calling a stored procedure, using a parameter of adDBTimeStamp, Native Client appears to treat the timestamp as a smalldatetime, rather than a datetime. This is causing us problems as we use 31 Dec 9999 as a "top-end" date in some comparisons, and Native Client fails with an "Invalid date format" error where SQLOLEDB had no issues.

Now it looks like we may just be able to change from adDBTimeStamp to adDate as the datatype when creating the parameter, however I was wondering if there was something we were missing in the connection string before we go ahead and make the code change.

VBScript code to reproduce below. For the avoidance of doubt, date format is UK (dd/mm/yyyy) before someone suggests we should be using 12/31/9999 :-) But also to confirm, the CDate doesn't fail.

Set db = CreateObject("ADODB.Command")

' If Provider=SQLOLEDB then there is no error.
db.ActiveConnection = "Provider=SQLNCLI11; Server=host; Database=bob; Integrated Security=SSPI; DataTypeCompatibility=80;"
db.CommandText = "usp_FetchData"
db.CommandType = &H0004

' 135 is adDBTimeStamp
db.Parameters.Append db.CreateParameter("@screenDate", 135, 1, 20, CDate("31/12/9999"))

Set rs = CreateOBject("ADODB.RecordSet")
' Then this line fails with "Invalid date format" from SQLNCLI
rs.Open db

WScript.Echo rs.RecordCount

Winding the datetime back to 2078 (within the date range of smalldatetime) makes the error go away.

As mentioned, if a non-code-change fix can be found, that's what we'd prefer, before we go and have to change adDBTimeStamp to adDate. I'd have expected DataTypeCompatiblity=80 to behave as SQLOLEDB; unfortantly my Google-fu has failed when finding out exactly what type mapping SQLNCLI uses.

Answer 1

A solution may have finally been found: via the MSDN page Data Type Support for OLE DB Date and Time Improvements, there is this snippet towards the end:

Data Type Mapping in ITableDefinition::CreateTable

The following type mapping is used with DBCOLUMNDESC structures used by ITableDefinition::CreateTable:

[...table of conversions...]

When an application specifies DBTYPE_DBTIMESTAMP in wType, it can override the mapping to datetime2 by supplying a type name in pwszTypeName. If datetime is specified, bScale must be 3. If smalldatetime is specified, bScale must be 0. If bScale is not consistent with wType and pwszTypeName,DB_E_BADSCALE is returned.

In testing, it appears this need to set the scale applies to parameters to SELECT and commands as well as to CreateTable. Therefore if we change the script above to:

Set param = db.CreateParameter("@screenDate", 135, 1, 20, CDate("31/12/9999"))
param.NumericScale = 3
db.Parameters.Append param

...then the error goes away and the stored procedure is executed. We're in the early stages of testing, however welcome feedback from others if this has caused issues.