PRB: Sybase Query Returns ASP 0101: 80070057 "Unexpected Error"

• Active Server Pages, included with:
  • Microsoft Internet Information Server versions 3.0, 4.0, 5.0
• Microsoft Data Access Components versions 2.0, 2.1, 2.5

SYMPTOMS

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:

ASP 0101: 80070057 "Unexpected error"

CAUSE

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.

RESOLUTION

There are four possible workarounds:

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

  
  SELECT NumField FROM Table 

  you would code the following:

  
  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. There are other companies that also supply OLE DB providers for Sybase, which can be found at http://www.microsoft.com/data/oledb/products/product.htm.



• Revert to MDAC 1.5 and use the 1.5 version of MSDASQL that does not bind Numeric data as Numeric.

STATUS

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

The third-party products discussed here are manufactured by vendors independent of Microsoft; we make no warranty, implied or otherwise, regarding these products' performance or reliability.

Additional query words:

Keywords : kbADO kbDatabase kbGrpASP kbDSupport
Version : WINDOWS:2.0,2.1,2.5; winnt:3.0,4.0,5.0
Platform : WINDOWS winnt
Issue type : kbprb