Q240882: PRB: SP Calls May Result in Attentions Being Seen on SQL Server

Article: Q240882
Product(s): Open Database Connectivity (ODBC)
Version(s): 3.5,3.6,3.7,6.5,7.0
Operating System(s): 
Keyword(s): kbODBC kbODBC350 kbODBC360 kbSQLServ650 kbSQLServ700 kbGrpDSVCDB kbGrpDSMDAC kbODBC370
Last Modified: 23-AUG-2001

-------------------------------------------------------------------------------
The information in this article applies to:

- Microsoft ODBC Driver for SQL Server, versions 3.5, 3.6, 3.7 
- Microsoft SQL Server versions 6.5, 7.0 
-------------------------------------------------------------------------------

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<AE> 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

Additional query words:

======================================================================
Keywords          : kbODBC kbODBC350 kbODBC360 kbSQLServ650 kbSQLServ700 kbGrpDSVCDB kbGrpDSMDAC kbODBC370 
Technology        : kbSQLServSearch kbAudDeveloper kbSQLServ700 kbSQLServ650 kbODBCSearch kbODBCSQLServ350 kbODBCSQLServ360 kbODBCSQLServ370
Version           : :3.5,3.6,3.7,6.5,7.0
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.