PRB: Server-side Cursors Are Forced with More Than One Command Object

ID: Q173410

The information in this article applies to:

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.

Server-side cursors will be implicitly forced with any queries, even they are if not requested by the application.

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"

Using the ODBC Provider, an OLE DB application is like an ODBC application. The ODBC Provider translates OLE DB interface requests into ODBC APIs, and then sends ODBC requests to ODBC drivers. OLE DB session objects are mapped to ODBC connections, and command objects are mapped to statements.

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)

With server-side cursors being enforced, an application cannot execute SQL statements that generate more than one resultset. SQL Server only allows executing server-side cursors with SQL statements that generate a single resultset. At this point, the application will have to release all the command objects except one, so that server-side cursor will not be enforced. Then the multiple-resultset SQL statements can be executed successfully.

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

Q156500 INF: Processing Procedures and Batches with Multiple SELECTS

Additional query words: busy oledb

Keywords : kbprg SSrvProg odbcCursor
Version : WINDOWS:1.1; :1.1
Platform : WINDOWS
Issue type : kbinfo