INFO: SQL Server Driver Version 3.70 May Return Fractional Truncation Message When Processing DateTime Parameter

• Microsoft SQL Server version 7.0

SUMMARY

When processing a parameterized query involving a datetime field, the Microsoft SQL Server 7.0 Driver version 3.70.06.23 will return the following error message(s):

DIAG [01004] [Microsoft][ODBC SQL Server Driver]Fractional truncation (0)

[Microsoft][ODBC SQL Server Driver]DateTime Field Overflow

MORE INFORMATION

The driver will pass parameterized statements to the server, creating input variables based on its knowledge of the parameter. Previous versions of the Microsoft SQL Server ODBC driver would take the value of the variable and insert it into the statement that it was executing, not having to create a variable to pass it to the server. In making the decision on what type of datatype to use to create the parameter, the 3.70.06.23 driver looks at the ColumnSize argument in the SQLBindParameter call. In past drivers, it was not unusual to pass in the size of the TIMESTAMP_STRUCT being passed to the driver. That worked without any problem since the driver took the data from the struct and passed it to the server. Now, the 3.70.06.23 driver looks at the size parameter and uses it to determine which datatype to use for the parameter, smalldatetime or datetime. Smalldatetime has a length of 16, datetime a length of 23 on the server. The struct has a length of 16, so when the driver sees a ColumnSize argument of 16 it assumes it is going to a 16 byte field, and creates a smalldatetime parameter. When it actually gets the data to pass to the server, it sees that it is passing a full datetime value to a smalldatetime variable and truncates the seconds and fractions and then it displays the informative message regarding Fractional truncation.

This behavior creates two problems. The first is that the entire datetime value is not being passed, and the second is the informative message. The best way to resolve this problem is to pass the correct ColumnSize argument in the SQLBindParameter call (the actual size of the column in the table, as specified in the SQLBindParameter spec). In the case of the datetime field that would be a 23.

A simple workaround for an existing executable that does not require it to be modified and recompiled is to use odbccmpt and instruct the driver to act as a 6.5 driver for that particular application.

REFERENCES

SQL 7.0 Books OnLine, search on: "odbccmpt.exe"

Additional query words:

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo