Parameterized Command Fails With Sybase ODBC Driver

When using the Sybase System 11 ODBC Driver 3.11.00.01 with MDAC 2.1 or later, you get the following error when attempting to execute a parameterized command within a transaction: This error did not occur when using the same code and the same Sybase ODBC driver with MDAC 2.0.

The Sybase System 11 ODBC Driver when used with the default settings does not allow more than one active HSTMT property per connection while in a transaction. When executing a parameterized command containing one or more variable length parameters, the Microsoft OLEDB Driver For ODBC (MSDASQL) that ships with MDAC 2.1 attempts to create a second HSTMT property, to gather more information on the parameters, resulting in the error.

Modify the ODBC DSN for the Sybase System 11 ODBC driver by setting the "Select Method" Configuration parameter to "0 - Cursor" to allow the driver to support more than one active HSTMT property per connection while in a transaction. You can also set this in the ADO connection string by adding "SelectMethod=0;" to the connection string.

This is a design change in the Microsoft OLE DB Provider For ODBC Drivers (MSDASQL) that ships with MDAC 2.1. The new version of MSDASQL driver now attempts to gather more detailed information about parameters in certain situations in order to more accurately bind parameters. The MDAC 2.0 version of the MSDASQL driver made fixed assumptions about the parameters and did not poll the ODBC driver for more detailed parameter information.