Problem:

When using client-side cursors with SQL Server 2000 or earlier, there is a limitation of only allowing one active cursor per connection. Attempting to open a second client-side cursor will issue an error "connection is busy processing another hstmt".

It is not possible to use separate connections for each cursor as this could lead to locking issues.

Resolution:

SQL Server 2005 has implemented support Multiple Active Result Sets (MARS). MARS removes this constraint/limitation and allows multiple queries or stored procedures to be performed on a single connection.

In order to take advantage of MARS with Net Express, the program must be compiled with the following directives :

SQL(DBMAN=ODBC, TARGETDB=MSSQLSERVER, MARS)

or, if using a DSN-less connection, specify MARS_Connection=YES within the Connection String.

This capablility is provided with Net Express 4.0 all06n40 wrappack (and later).

Note that the ODBC DSN used must be the SQL Native Client driver, rather than the regular SQL Server ODBC driver.

If you are using SQL Server 2000, or are using the SQL Server ODBC driver, you can additionally compile with the SQL(BEHAVIOR) directive to enable use of IC_FH cursors. Refer to the documentation for the SQL(BEHAVIOR) directive for additional information.