After upgrading to Microsoft Data Access Components (MDAC) 2.0, some queries to a Sybase database using an Intersolv 3.x Sybase ODBC driver result in the following error:

This behavior occurs because the Intersolv 3.x Sybase ODBC driver is reporting back that it is an ODBC 3.0 compliant driver. Unfortunately, it is not fully ODBC 3.0 compliant.

When you make an ADO call to an ODBC datasource, ADO uses the Microsoft OLE DB Provider for ODBC by default, which is called MSDASQL. The old MSDASQL from MDAC 1.x did not test for ODBC 3.0 compliance. In cases where the field was of type Numeric, the old MSDASQL would bind the data as character data instead of Numeric data as in this case. The new MSDASQL still binds Numeric data as character data if an ODBC driver does not report back ODBC 3.0 compliance.

If the ODBC driver reports back ODBC 3.0 compliance, MSDASQL binds Numeric data as Numeric. This is where the problem occurs. The Intersolv 3.x driver reports back ODBC 3.0 compliance, so MSDASQL attempts to bind the Numeric data as Numeric. Unfortunately, when the data ends in "0", the data binding fails, resulting in the error.

There are four possible workarounds:

• Convert your numeric field to character data in your SQL statement. For example, instead of using the following code

  SELECT NumField FROM Table
  						

  you would use the following code:

  SELECT convert(char(20), NumField) FROM Table
  						

  This would return the numeric value, but as a type of Character.
• Use an older version of the Sybase ODBC driver. Version 2.12 of the Sybase ODBC driver does not report back ODBC 3.0 compliance, so MSDASQL binds Numeric data as Character data.
• Use Intersolv's OLE DB Provider for Sybase. Intersolv's OLE DB Provider for Sybase does not exhibit this behavior. You can download a trial version (30 day) from Intersolv.
• Revert to MDAC 1.5 and use the 1.5 version of MSDASQL that does not bind Numeric data as Numeric.

This behavior is by design and the problem resides in the third-party ODBC driver.

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.