Parameterized Command Fails With Sybase ODBC Driver

• Microsoft Data Access Components versions 2.1, 2.5

SYMPTOMS

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:

You can't have multiple recordsets with this cursor type (in a transaction.)

Either change the cursor type, commit the transaction, or close one of the record sets.

CAUSE

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.

RESOLUTION

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.

STATUS

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.

Additional query words:

Keywords : kb3rdparty kbADO kbDatabase kbODBC kbGrpMDAC kbDSupport kbMDAC210 kbMDAC210SP2 kbMDAC250
Version : WINDOWS:2.1,2.5
Platform : WINDOWS
Issue type : kbprb