Multiple statement handles on one connection

I want to use multiple concurrently active statement
handles on a single connection handle. An ODBC call to
SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
indicating that there is a limit of 1 active statement on
a single connection. If I try to SQLExecDirect on another
statement handle on the same connection handle I get an
error complaining that there is another active statement
on the same connection.
Is there any way to configure either SQLServer or its ODBC
driver to allow multiple concurrently active statement
handles on the same connection? This is pretty common
database programming practice to Exec one query, then use
a loop with SQLFetch and then execute multiple SQL
statements on another statement handle within the loop.
Even lowly MSAccess allows multiple concurrently active
statement handles on the same connection.
My ODBC driver version is 2000.85.1022.00.
My SQLServer version SQL Server Developer Edition 8.00.194
(RTM)This is not possible with current versions of SQL Server or the SQL Server
ODBC Driver, when using the default "firehose" cursor. You can use
server-side cursors, which allow you to fetch a single row at a time from
the server (and therefore free up the connection between each row), or you
can cache the data from the first statement yourself (therefore freeing up
the connection). And of course, you can open up a second connection for the
second statement.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Lee Scheffler" <anonymous@.discussions.microsoft.com> wrote in message
news:b47e01c4077d$ef011af0$a101280a@.phx.gbl...
> I want to use multiple concurrently active statement
> handles on a single connection handle. An ODBC call to
> SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
> indicating that there is a limit of 1 active statement on
> a single connection. If I try to SQLExecDirect on another
> statement handle on the same connection handle I get an
> error complaining that there is another active statement
> on the same connection.
> Is there any way to configure either SQLServer or its ODBC
> driver to allow multiple concurrently active statement
> handles on the same connection? This is pretty common
> database programming practice to Exec one query, then use
> a loop with SQLFetch and then execute multiple SQL
> statements on another statement handle within the loop.
> Even lowly MSAccess allows multiple concurrently active
> statement handles on the same connection.
> My ODBC driver version is 2000.85.1022.00.
> My SQLServer version SQL Server Developer Edition 8.00.194
> (RTM)