Using IMultipleResults to Process Multiple Result Sets
In general, consumers should use the IMultipleResults interface to process the rowset or rowsets returned by SQLOLEDB command execution.
When SQLOLEDB submits a command for execution, Microsoft® SQL Server™ 2000 executes the statement or statements and returns any results. The complete process is a round trip between the client and the instance of SQL Server. Each client connection to an instance of SQL Server can have at most one active round trip. That is, within a SQLOLEDB session, only a single command object can be actively executing or returning results on the connection. This is the default result set behavior of SQL Server client connections.
To complete a round trip, a client must process all results from command execution. Because SQLOLEDB command execution can generate multiple-rowset objects as results, use the IMultipleResults interface to ensure that application data retrieval completes the client-initiated roundtrip.
The following Transact-SQL statement generates multiple rowsets, some containing row data from the OrderDetails table and some containing results of the COMPUTE BY clause:
SELECT OrderID, FullPrice = (UnitPrice * Quantity), Discount,
Discounted = UnitPrice * (1 - Discount) * Quantity
FROM OrderDetails
ORDER BY OrderID
COMPUTE
SUM(UnitPrice * Quantity), SUM(UnitPrice * (1 - Discount) * Quantity)
BY OrderID
If a consumer executes a command containing this text and requests a rowset as the returned results interface, only the first set of rows is returned. The consumer may process all rows in the rowset returned but if the DBPROP_MULTIPLECONNECTIONS data source property is set to VARIANT_FALSE, until the command is canceled, no other commands can be executed on the session object (SQLOLEDB will not create another connection). SQLOLEDB returns a DB_E_OBJECTOPEN error if DBPROP_MULTIPLECONNECTIONS is VARIANT_FALSE and returns E_FAIL if there is an active transaction.
If the connection is busy running a command that does not produce a rowset or produces a rowset that is not a server cursor and the DBPROP_MULTIPLECONNECTIONS data source property is set to VARIANT_TRUE, SQLOLEDB creates additional connections to support concurrent command objects unless a transaction is active, in which case it returns an error. Transactions and locking are managed by SQL Server 2000 on a per connection basis. If a second connection is generated, the command on the separate connections do not share locks. Care must be taken to ensure that one command does not block another by holding locks on rows requested by the other command.
The consumer can cancel the command either by using ICommand::Cancel or by releasing all references held on the command object and the derived rowset.
Using IMultipleResults in all instances allows the consumer to get all rowsets generated by command execution and allows consumers to appropriately determine when to cancel command execution and free a session object for use by other commands.
Note When you use SQL Server 2000 cursors, command execution creates the cursor. SQL Server 2000 returns success or failure on the cursor creation; therefore, the round trip to the instance of SQL Server is complete upon the return from command execution. Each GetNextRows call then becomes a round trip. In this way, multiple active command objects can exist, each processing a rowset that is the result of a fetch from the server cursor. For more information, see Rowsets and SQL Server Cursors.