Thursday 1 October 2009

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.

9 comments:

  1. I am having a similar problem except the fact that i don't get an error but my datetime seconds gets rounded to 00. I am developping in vbscript and using parameters with stored procedure. Any idea on how i can change the settings?

    cheers

    ReplyDelete
  2. My column is DATETIME2(6).
    ColumnSize = 23 or 19
    DecimalDigits = 3
    => it works

    ColumnSize = 26
    DecimalDigits = 6
    => Failed: SQLSTATE=HY104; Native=0; Msg=[Microsoft][ODBC SQL Server Driver]Invalid precision value
    Does the DecimalDigits only work when =< 3? Please help. Thanks you.

    ReplyDelete
  3. For those of you who don't like being in the dark about the meaning of magic numbers:

    "
    16 (the number of characters in the yyyy-mm-dd hh:mm format)
    19 (the number of characters in the yyyy-mm-dd hh:mm:ss format)
    or
    20 + s (the number of characters in the yyyy-mm-dd hh:mm:ss[.fff...] format, where s is the seconds precision)."

    From the docs on Column Size: https://msdn.microsoft.com/en-us/library/ms711786(v=vs.85).aspx

    ReplyDelete
  4. We just upgraded to SQL Server Native Client 11.0 - and I got the same errors? any ideas how to fix this?
    my field - prec(23) - scale(3)

    ReplyDelete
  5. You can try this


    SQL_DATE_STRUCT date; // date structure
    SQLLEN cbdate; // size of date structure
    cbdate = sizeof(SQL_DATE_STRUCT);

    ...

    rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, SQL_TYPE_DATE, 10, 0, &date, 0, &cbdate);
    check_rc(rc);

    // For new datetime2 type
    SQL_TIMESTAMP_STRUCT datetime2; // datetime2 structure
    SQLLEN cbdatetime2; // size of datetime2
    cbdatetime2 = sizeof(SQL_TIMESTAMP_STRUCT);

    ...

    rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TYPE_TIMESTAMP, 27, 7, &datetime2, 0, &cbdatetime2);
    check_rc(rc);

    ReplyDelete
  6. I am having related problems. I have a classic-ASP app that talks to SQL Server 2008 via SQLNCLI10 just fine. I had to disable TLS 1.0 for security compliance and couldn't get SQLNCLI10 to connect (even after all TLS 1.2 hotfixes/patches/etc), so I created an ODBC DNS which connects via TLS 1.2. When using Microsoft ODBC Driver for SQL Server Version 13.00.0811 to connect I'm able to connect just fine, but there are issues with selecting from the datetime fields. the resulting object returned isn't handling datetimes correctly.

    There are datetime fields in the database like: 1998-08-23 00:00:00.000

    ReplyDelete