- Method 1: Flush all the output result sets. As soon as all output result sets are consumed by the client, SQL Server completes executing the batch.
- If you are using Microsoft Open Database Connectivity (ODBC) to connect to SQL Server, you can call the SQLMoreResults method until the method reports that there are no more result sets.
- If you are using Microsoft OLE DB to connect to SQL Server, you can repeatedly call the IMultipleResults::GetResult method until it returns DB_S_NORESULT .
- Method 2: Add the statement SET NOCOUNT ON to the beginning of your batch. If the batch is executed inside a stored procedure, add the statement to the beginning of the stored procedure definition. This prevents SQL Server from returning many types of result sets. Therefore, it can reduce the data to be output to the output buffer of the server. However, this does not guarantee that the problem will not occur. It only increases the chance that the data that is returned from the server is small enough to fit into one batch of result sets.
- Microsoft recommends that you always consume all result sets from SQL Server regardless of the size of the batch that you are executing. If you do not flush this data and there are successful result sets to be returned ahead of the error in the result set batch, the client might not discover the server errors.
- Client applications should flush the result sets to guarantee correct execution.
Steps to reproduce the problem
The problem is most easily reproduced when you are connected to the SQL Server by using the Named pipes protocol or the Shared memory (LPC) protocol. This is because of the internal buffer size that SQL Server has available for the different protocols.
The following are the possible effects of this problem. The effects are varied and depend on exactly what your batch contains.
- Consider that a batch of database query statements is executed from an application. If the batch of database query statements is made up of a "BEGIN TRANSACTION" at the beginning and "COMMIT TRANSACTION" at the end, the commit operation may not occur even though the control is returned to the application. This is an issue because the locks that are possibly being held may cause a pending transaction and may remain unnoticed.
In this scenario, because the transaction is never committed in the batch, it remains pending and is rolled back on disconnection from the SQL Server.
- If you use an application program interface (API) to begin and commit your transaction, you may see the following behavior:
- If you use the API to send a notification to the server to start a transaction, and then you execute the batch, SQL may process only a part of the batch, and then return the control to the application.
- After this step, if you use the API to commit the transaction, only the part of the batch that was processed is committed. No error occurs.
For example, with ODBC you call SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) to start the transaction, and then you use SQLEndTran(SQL_COMMIT) to commit the transaction.
For additional information about how to process multiple results in ODBC, visit the following MSDN Web site:SQLMoreResults API, visit the following MSDN Web site:GetResult method of the OLE DB IMultipleResults interface, visit the following MSDN Web site:
ID do Artigo: 827575 - Última Revisão: 26 de ago de 2008 - Revisão: 1