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.

Back to the top

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:

Back to the top

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.

Back to the top

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.

Back to the top