SQL Server Native Client 10.0 - Datetime field overflow

I got a nasty surprise when one of my apps failed when configured to use the latest SQL Server ODBC driver. The code is 6 years old and has been running without a problem in all that time  and has been used with all kinds of databases and different versions of Windows.
The app tries to insert a value into a datetime field and the following error is reported....
22008 [Microsoft][SQL Server Native Client 10.0]
Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.
As I said the code runs fine against all databases including SQL Server 2008, when using the SQL Server 2005 Client. It is just the  SQL Server Native Client 10.0 driver that fails.
After quite a bit of searching I came across a forum posting with this explanation....
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/ac1b5a6d-5e64-4603-9c92-b75ba4e51bf2
For ODBC the rule for all types is that truncation on input is an error and truncation on output is a warning see http://msdn.microsoft.com/en-gb/library/ms716298(VS.85).aspx for details.
Earlier ODBC drivers for SQL Server could infer the server type (datetime or smalldatetime) from the scale (which had to be 0 or 3) and so could be more relaxed than SQL Server 2008 Native Client. The default scale for OdbcParameter is 0, and so earlier drivers could assume the server type must be smalldatetime and ignore any fractional seconds. With the introduction of datetime2 and a user defined scale of between 0 and 7 the driver can no longer infer the type from the scale and has to default to the richest type, datetime2. When the actual server type is not datetime2 there will be a server side conversion from datetime2 to the actual server type. I apologise for the invonvenience this has caused you, but we had little choice and the new behavior is documented.
With the addition of variable scale for datetime2 in SQL Server 2008 we had to tighten up on parameter validation for SQL_TYPE_TIMESTAMP to avoid the possibility that an application could unwittingly suffer data corruption. The is documented in Books Online at http://msdn.microsoft.com/en-us/library/bb964722(SQL.100).aspx
which says
Stricter SQL_C_TYPE _TIMESTAMP and DBTYPE_DBTIMESTAMP parameter validation.
Prior to SQL Server 2008 Native Client, datetime values were rounded to fit the scale of datetime and smalldatetime columns by SQL Server. SQL Server 2008 Native Client now applies the stricter validation rules that are defined in the ODBC core specification for fractional seconds. If a parameter value cannot be converted to the SQL type by using the scale specified or implied by the client binding without truncation of trailing digits, an error is returned.
The default scale of OdbcParameter is 0. You need to ensure that the scale of the parameter and its value match. If the scale of the datetime2 column is 7 (which is the default for datetime2), then the following line of code is required.
cmnd.Parameters[1].Scale = 7; 
In other words they have changed the way things work and broken backward compatibility.
In my case I was using C++ to directly access the C ODBC API.
This is the relevant bits of code.

SQLSMALLINT ValueType,ParameterType,DecimalDigits = 0;
SQLUINTEGER ColumnSize = 0;
SQLINTEGER     BufferLength = 0;
SQL_TIMESTAMP_STRUCT d;
....
ValueType = SQL_C_TYPE_TIMESTAMP;
ParameterType = SQL_TYPE_TIMESTAMP;
ColumnSize = 23;
....
pT = (const sXPTimeStructured *)(paramValues[i]);
....
d.year = pT->Year();
d.month = pT->Month();
d.day = pT->Day();
d.hour = pT->Hour();
d.minute = pT->Minute();
d.second = pT->Second();
d.fraction = pT->Milliseconds();
d.fraction *= 1000000;
StrLen[i] = 0;
BufferLength = 0;
....
r = SQLBindParameter(hstmt,i+1,SQL_PARAM_INPUT,ValueType,ParameterType,ColumnSize,
DecimalDigits, &(ParameterValue[i]),BufferLength,&(StrLen[i]));
....
r = SQLExecute(hstmt);

The SQLBindParameter would always work, the error was generated when running the execute....


If  d.fraction is 0 then there is no problem, otherwise the 22008 Datetime field overflow message is produced.


The solution is to add the following line before the call to  SQLBindParameter.

DecimalDigits = 3;

This seems to tell the ODBC driver that the scale of the second fraction is set 3 decimal places and everything works as it did before using the new MS SQL 10.0 driver.


Despite the MS claims I cannot find this documented anywhere, so I do not feel bad about not putting it in six years ago. Now I think about it the most remarkable thing about all this is that this is the first time this code has been broken by a backward compatibility issue.