Microsoft KB Archive/157570

= FIX: Temp Table Stranded If Deadlock in Stored Proc. w/ Cursor =

Article ID: 157570

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q157570



BUG #: 16037 (Windows NT, 6.50)



SYMPTOMS
If a stored procedure is selected as the victim in a deadlock situation, a temporary table created within the procedure may become stranded in tempdb. This situation will only occur if, in addition to the temporary table, a cursor is declared within the stored procedure, and the stored procedure then acts upon the temporary table.

Because the standard method for handling a deadlock is to resubmit the command that was terminated, problems may arise if that command attempts to re-create the temporary table upon resubmission.

Specifically, error message 2714 may be reported when the query is resubmitted, as follows:

SQL Server message 2714, state 1, severity 16:

There is already an object named '#temp' in the database.

Attempts to drop the temporary table prior to re-creating it will fail, and you will receive error message 3701:

SQL Server message 3701, state 1, severity 11:

Cannot drop the table '#temp', because it doesn't exist in the system

catalogs.

The existence of the temporary table can be confirmed by selecting from tempdb.sysobjects for that table.

This problem does not occur if a cursor is not used within the stored procedure. Thus, if a cursor is absent, the temporary table is correctly cleaned up from tempdb after a deadlock.



WORKAROUND
To work around this problem, try to close out the connection on which the deadlock occurred, prior to resubmitting the command. It is not necessary to cycle SQL Server in order to clean up an object left stranded in this manner, because that process (and any associated resources) appear to be released once the creating process is closed.



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.



MORE INFORMATION
On SQL Server 6.0, the temporary table was not left stranded after a deadlock. However, an attempt to declare and open the cursor fails, with the following error message:

SQL Server message 16915, state 1, severity 16:

A cursor with the name 'cur1' already exists.

This error message is then followed by the following error:

SQL Server message 16905, state 1, severity 16:

The cursor is already open.

Keywords: kbbug kbfix kbnetwork KB157570

-

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

© Microsoft Corporation. All rights reserved.