Edit

Share via

Using IMultipleResults to Process Multiple Result Sets in SQL Server Native Client

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Consumers use the IMultipleResults interface to process results returned by SQL Server Native Client OLE DB provider command execution. When the SQL Server Native Client OLE DB provider submits a command for execution, SQL Server executes the statements and returns any results.

A client must process all results from command execution. Because the SQL Server Native Client OLE DB provider 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, and MARS is not enabled on the connection, no other commands can be executed on the session object (the SQL Server Native Client OLE DB provider will not create another connection) until the command is canceled. If MARS is not enabled on the connection, the SQL Server Native Client OLE DB provider returns a DB_E_OBJECTOPEN error if DBPROP_MULTIPLECONNECTIONS is VARIANT_FALSE and returns E_FAIL if there is an active transaction.

The SQL Server Native Client OLE DB provider will also return DB_E_OBJECTOPEN when using streamed output parameters and the application has not consumed all the returned output parameter values before calling IMultipleResults::GetResults to get the next result set. If MARS is not enabled and the connection is busy running a command that does not produce a rowset or that produces a rowset that is not a server cursor, and if the DBPROP_MULTIPLECONNECTIONS data source property is set to VARIANT_TRUE, the SQL Server Native Client OLE DB provider 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 on a per connection basis. If a second connection is generated, the command on the separate connections does 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. If MARS is enabled, multiple commands can be active on the connections and if explicit transactions are being used, the commands all share a common transaction.

The consumer can cancel the command either by using ISSAbort::Abort 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 cursors, command execution creates the cursor. SQL Server 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.

See Also

Commands