Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/173410

From BetaArchive Wiki
Knowledge Base


INFO: Server-Side Cursors Are Forced with More Than One Command Object

Article ID: 173410

Article Last Modified on 10/15/2003



APPLIES TO

  • Microsoft OLE DB 1.1
  • Microsoft OLE DB Provider for ODBC 1.1



This article was previously published under Q173410

SUMMARY

The OLE DB Provider for ODBC 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 OLE DB Provider for ODBC, an OLE DB application is like an ODBC application. The OLE DB Provider for ODBC 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 OLE DB Provider for ODBC 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 OLE DB Provider for ODBC 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 OLE DB Provider for ODBC, 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:

156500 INF: Processing Procedures and Batches with Multiple SELECTS



Additional query words: busy oledb

Keywords: kbinfo kbprogramming KB173410