MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server
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, SQL Server executes the statement or statements and returns any results. The complete process is a round-trip between the client to the SQL Server. Each client connection to a 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 round-trip.
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, no other commands can be executed on the session object until the command is canceled. (SQLOLEDB won’t 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 an error is returned. Transactions and locking are managed by SQL Server 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 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 using SQL Server cursors, command execution creates the cursor. SQL Server returns success or failure on the cursor creation, therefore the round-trip to the SQL Server is complete upon the return from command execution. Each IRowset::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.