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

Last reviewed: September 4, 1997
Article 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:

ARTICLE-ID: Q156500

TITLE     : INF: Processing Procedures and Batches with Multiple SELECTS


Additional query words: busy oledb
Keywords : odbcCursor SSrvProg kbprg
Version : WINDOWS:1.1.0.2326
Platform : WINDOWS
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 4, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.