Using SQL Server Default Result Sets

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

The default ODBC cursor attributes are:


Whenever these attributes are set to their defaults, the SQL Server Native Client ODBC driver uses a SQL Server default result set. Default result sets can be used for any SQL statement supported by SQL Server, and are the most efficient method of transferring an entire result set to the client.

SQL Server 2005 (9.x) introduced support for multiple active result sets (MARS); applications can now have more than one active default result set per connection. MARS is not enabled by default.

Before SQL Server 2005 (9.x), default result sets did not support multiple active statements on the same connection. After an SQL statement is executed on a connection, the server does not accept commands (except a request to cancel the rest of the result set) from the client on that connection until all the rows in the result set have been processed. To cancel the remainder of a partially processed result set, call SQLCloseCursor or SQLFreeStmt with the fOption parameter set to SQL_CLOSE. To finish a partially processed result set and test for the presence of another result set, call SQLMoreResults. If an ODBC application attempts a command on a connection handle before a default result set has been completely processed, the call generates SQL_ERROR and a call to SQLGetDiagRec returns:

szSqlState: "HY000", pfNativeError: 0  
szErrorMsg: "[Microsoft][SQL Server Native Client]  
                Connection is busy with results for another hstmt."  

See Also

How Cursors Are Implemented