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

Last reviewed: April 9, 1997
Article ID: Q157570
The information in this article applies to:
  • Microsoft SQL Server, versions 6.0 and 6.5
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.


Additional query words:
Keywords : kbbug6.00 kbbug6.50 kbfix6.50.sp2 kbnetwork SSrvStProc
Version : 6.0 6.5
Platform : WINDOWS


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.

Last reviewed: April 9, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.