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

• Microsoft SQL Server, versions 6.0 and 6.5

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.

   SQL Server message 3701, state 1, severity 11:
   Cannot drop the table '#temp', because it doesn't exist in the system
   catalogs.

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.

   SQL Server message 16905, state 1, severity 16:
   The cursor is already open.