PRB: Server-side Cursors Are Forced w/More Than One Command Obj.

• Microsoft OLE DB, version 1.1
• Microsoft ODBC Provider, version 1.1

SUMMARY

The ODBC Provider may force the use of server-side cursors if both of the following conditions are true:

• The backend database server is Microsoft SQL Server

  -and-

• More than one Command object exists within the same session object.

MORE INFORMATION

The Microsoft SQL Server ODBC driver can have only one active statement unless server-side cursors are used. An ODBC application may receive the following error message if it attempts to have more than one active statement without using server-side cursors:

   szSqlState = "S1000", *pfNativeError = 0
   szErrorMsg = "[Microsoft][ODBC SQL Server Driver]Connection is busy with
   results for another hstmt"

Dealing with the SQL Server ODBC driver, the ODBC Provider forces the use of server-side cursors if there is more than one command object or statement opened. With more than one command object or statement opened, the application may run into the one active statement limitation. Therefore, the ODBC Provider automatically uses server-side cursors to avoid the limitation. The type of server-side cursor enforced by the ODBC Provider is forward-only and read-only. For example, the forward and read-only server-side cursors will be forced with the following code:

   IcommandText         *pICommandText1;
   IcommandText         *pICommandText1;
   Icommand             *pIDBCommand;
   WCHAR  *stmt_string = SysAllocString((LPOLESTR)L"SELECT * FROM TITLES");

   ...connect to the ODBC Provider, create session and command object .....

   pIDBCommand-
   >CreateCommand(NULL,IID_ICommandText,(IUnknown**)&pICommandText1)
   pIDBCommand-
   >CreateCommand(NULL,IID_ICommandText,(IUnknown**)&pICommandText2)

   pICommandText1->SetCommandText ( DBGUID_DBSQL,  stmt_string )
   pICommandText1->Execute(NULL, IID_NULL, NULL, NULL,NULL)

For additional information for server-side cursors and multiple resultsets, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q156500

TITLE     : INF: Processing Procedures and Batches with Multiple SELECTS