Microsoft KB Archive/310617

= FIX: Sp_reset_connection Does Not Reset the Rowcount Settings for the DELETE and UPDATE Statements =

Article ID: 310617

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q310617



BUG #: 355872 (SHILOH_BUGS)



SYMPTOMS
When you use connection pooling to connect to SQL Server, SQL Server calls the sp_reset_connection stored procedure before re-using a connection from the pool. Use of the sp_reset_connection stored procedure ensures that the connection options are reset so that the client application has no persisting settings from the previous statements that were executed on that connection.

However, in Microsoft SQL Server 2000, the sp_reset_connection stored procedure may not reset the rowcount setting properly.

If you execute a DELETE or UPDATE statement executed after the sp_reset_connection stored procedure call, SQL Server respects the old rowcount value (in other words, the old rowcount setting persists). In this case, the DELETE or UPDATE statement only updates or deletes the number of rows that were specified in previous connection setting.

This problem does not occur with a SELECT statement. If you run a SELECT after you call the sp_reset_connection stored procedure, SQL Server ignores the rowcount value set in the previous connection, and the SELECT statement returns the expected number of rows.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem, in the application you can execute SET ROWCOUNT 0, before you start a new SQL Server connection from the pool. Use of SET ROWCOUNT 0 resets the rowcount for every new connection.



STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

Keywords: kbbug kbfix KB310617

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.