PRB: Stored Procedure Calls May Result in Attentions Being Seen on SQL Server

Article translations Article translations
Article ID: 240882 - View products that this article applies to.
This article was previously published under Q240882
Expand all | Collapse all

On This Page

SYMPTOMS

A SQL Server trace may reveal excessive Attentions and Rollbacks due to client disconnects. However, the client does not receive an error message.

CAUSE

SQL Server stored procedures, which are not expected to return a resultset, may return a large volume of informational data (DONE_IN_PROC messages) that are occasionally sufficient to overrun a packet buffer.

When this happens, the SQL Server driver issues a disconnect. The most visible evidence of this disconnect is an Attention showing up in a SQL Server trace.

RESOLUTION

Modify the SQL Server stored procedure so the first instruction is SET NOCOUNT ON. This prevents the majority of DONE_IN_PROC messages from being sent back to the client and avoids the packet buffer overflow.

STATUS

This behavior is by design.

MORE INFORMATION

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft SQL Server to execute queries, the results prevent "nn rows affected" from being displayed at the end of Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return much actual data this can provide a significant performance boost because network traffic can be reduced. The setting of SET NOCOUNT is set at execute or run-time and not at parse time.

Examples

This example (when executed in the osql utility or SQL Server Query Analyzer) prevents the message (about the number of rows affected) from being displayed:

USE pubs

GO

-- Display the count message.

SELECT au_lname 

FROM authors

GO

USE pubs

GO

-- SET NOCOUNT to ON and no longer display the count message.

SET NOCOUNT ON

GO

SELECT au_lname 

FROM authors

GO

-- Reset SET NOCOUNT to OFF.

SET NOCOUNT OFF

GO
				

Properties

Article ID: 240882 - Last Review: September 17, 2003 - Revision: 5.2
APPLIES TO
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbprb KB240882

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com