[MSSQL] 시퀄 2008 오늘의 삽질 (Datetime field overflow)

시퀄 2008 에서는 2005와 달리 ODBC 버전에 의해(?) Datetime -> SmallDatetime 필드에 대입되면 에러가 떨어집니다. 반가운 Overflow 에러 ;;

이건 로컬에서는 로컬테이블에 데이터 입출력을 할때는 영향이 없는것 같은데... 

Linked Server를 사용해서 다은 테이블의 SmallDatetime 컬럼에 Getdate() 등을 이용해 Datetime 값을 입력하게 되면 발생합니다. 

해결 방법은 아직 찾지 못했고 그냥... 자알~ 이정도...

여하튼... 당황스러운 경우입니다. 

밑에는 이에 대한 내용을 누군가 정리해 놔서 가져다가 붙였습니다.  참고하셔서 저같은 일을 겪지 않으시길 ㅎㅎㅎ

출처 : http://rightondevelopment.blogspot.com/2009/10/sql-server-native-client-100-datetime.html

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 seehttp://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.

Posted by Tom Wright at 17:47  

Labels: C++, ODBC