SQL Client Data Type Compatibility - Incompatible with SQLOLEDB

Question

We had a problem with our legacy application when switching to using SQL Native Client (SQLNCLI) as an ADO provider.

Our original connection string:

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

we changed this to:

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

We found that when calling a stored procedure using the adDBTimeStamp parameter, the Native Client seems to treat the timestamp as smalldatetime rather than datetime. This causes us problems because we use December 31, 9999 as the "end" date in some comparisons, and the Native Client fails with the error "Invalid date format" when SQLOLEDB did not detect any problems.

Now it looks like we can just change the adDBTimeStamp attribute to adDate as the data type when creating the parameter, however, I was wondering if there was anything we did not see in the connection string before we go ahead and make the code changes.

VBScript code to play below. For the avoidance of doubt, the date format is Great Britain (dd / mm / yyyy), before someone tells me that we should use 12/31/9999 :-) But for confirmation, CDate does not 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

Disabling the date and time before 2078 (within the smalldatetime date range) makes the error missed.

, , , , adDBTimeStamp adDate. , DataTypeCompatiblity = 80 SQLOLEDB; , Google-fu , , SQLNCLI.

Answer 1

, : MSDN OLE DB, :

ITableDefinition:: CreateTable

DBCOLUMNDESC ITableDefinition:: CreateTable:

[... ...]

DBTYPE_DBTIMESTAMP wType, datetime2, pwszTypeName. datetime, bScale 3. smalldatetime , bScale 0. bScale wType pwszTypeName DB_E_BADSCALE.

, , , SELECT , CreateTable. , script :

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

... . , , .