The information in this article applies to:
SYMPTOMS
Using SQL Server ODBC driver and SQL Server and if ODBC prepared execution
is used, certain floating point values may be incorrectly converted. NOTE:
Microsoft Access and Microsoft Visual Basic commonly use the ODBC prepared
execution.
However, the following query shows the row with col1=2.9
This does not happen when the insert is done from a DB-Library (DB-Lib)
client tool, or if direct, non-prepared execution is used. For the SQL
Server driver, if the users do not have CREATE PROC permission, direct, non-prepared execution is used.
In case of Microsoft Access and Visual Basic, the behavior is exhibited by doing the insert into either an attached table or by creating the dynaset, followed by running the query to retrieve the records where float column equals the value inserted. For example,
Then a query which checks for equality of the float column to the value
inserted does not show the record inserted above, whereas a non-qualified
query shows the record. For example, the recordset for the ds1 dynaset does
not show the record inserted, whereas ds2 dynaset will.
For an ODBC application writer, the following steps will produce the
behavior:
And then retrieve the result to see that the record inserted above does not
show up. However, executing the following shows the record inserted above:
CAUSEThe difference in behavior is because in the case of prepared execution, the ODBC driver is doing the conversion to float; whereas in the case of non-prepared execution and DB-Lib client tools, SQL Server is doing the conversion. WORKAROUNDTo workaround this problem, you can do an explicit convert on the SQL Server using a statement similar to the following:
You can also do the same thing within a trigger to automatically update the
value for all new records inserted.
Please note that this problem does not occur using pass-through mechanism since in that case, the conversion is done by SQL Server. Additional query words: 1.01.2807 VB DBLibrary stored procedure
Keywords : |
Last Reviewed: August 25, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |