MORE INFORMATION
This information applies to Microsoft SQL Server ODBC Drivers version
2.50.0121 or later when running against Microsoft SQL Server version 6.0 or
later. Earlier versions of the Microsoft driver or Microsoft SQL Server do
not support multiple active statements on a connection handle. If you are
using third party SQL Server ODBC drivers, you should review the
documentation accompanying the driver to see if it supports multiple active
statements with SQL Server 6.0.
Prior to SQL Server 6.0, Microsoft SQL Servers did not support multiple
active statement handles on a single ODBC connection handle. This is due to
the architecture of SQL Server's network layers. After the server has built
a result set, it has to send the entire result set to the client before it
will accept a new operation from the client. Client fetch requests do not
have to be sent back to the server, they simply read the next row available
in the network buffer. The server will accept only one type of request from
a client before the result set has been completely sent -- a 'cancel'
command canceling the processing of the result set. Because of this
architecture, no SQL Server clients, either DB-Library or ODBC, can process
more than one result set at a time on a connection handle.
SQL Server 6.0 does not change this network architecture, but it does
introduce server-side cursors that can be used to open multiple cursors on
a single connection handle. This can be done because each cursor operation
in the ODBC driver generates one individual cursor command which is sent to
SQL Server. When the result set for each cursor command has been received
back at the client, SQL Server considers the command to have completed and
it will accept another command from another statement handle over that
connection handle.
For example, an application can:
SQLAllocEnv(&henv):
SQLAllocConnect(henv, &hdbc);
SQLAllocStmt(hdbc, &hstmt1);
SQLAllocStmt(hdbc, &hstmt2);
SQLSetConnectOption(hdbc, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC);
SQLSetConnectOption(hdbc, SQL_ROWSET_SIZE, 5);
SQLExecDirect(hstmt1, "select * from authors", SQL_NTS);
When the SQLExecDirect() on hstmt1 is executed, the Microsoft SQL Server
ODBC driver will issue a cursor open request. When SQL Server completes the
cursor open, it considers the command to be finished and will allow the
application to then issue a command on another hstmt:
SQLExecDirect(hstmt2, "select * from titles", SQL_NTS);
Once again, after the server has finished with the cursor open request
issued by the client, it considers the statement to be completed. If at
this point the ODBC application does:
SQLExtendedFetch(hstmt1, SQL_FETCH_NEXT, 1, ...);
the ODBC driver will send to SQL Server a cursor fetch for the first five
rows of the result set. Once the server has transferred the five rows to
the driver, it considers the fetch processing completed and will accept new
requests. The application could then do a fetch on the cursor opened for
the other statement handle:
SQLExtendedFetch(hstmt2, SQL_FETCH_NEXT, 1, ...);
SQL Server will accept this second command on the connection handle
because, as far as it is concerned, it has completed the last command on
the connection handle, which was the fetch of the first five rows of the
rows for hstmt1.
This process only works when the ODBC driver uses server cursors. As
documented in the Microsoft SQL Server ODBC Driver documentation, the
driver uses server cursors except when the following statement options are
set:
SQL_CONCURRENCY = SQL_CONCUR_READ_ONLY
SQL_CURSOR_TYPE = SQL_CURSOR_FORWARD_ONLY
SQL_ROWSET_SIZE = 1
The ODBC driver does use server-side cursors for all other options when
executing a single SELECT statement, or a stored procedure that contains
only a single SELECT statement. The current implementation of server
cursors in SQL Server 6.0 itself does not support batches of SQL statements
or stored procedures that have more than one SELECT statement.