FIX: Sp_reset_connection Does Not Reset the Rowcount Settings for the DELETE and UPDATE Statements (310617)

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.