BUG: Errors 2714 and 267 on INSERT INTO Global Temp Table

ID: Q166200

The information in this article applies to:

Microsoft SQL Server versions 7.0, 6.5

BUG #: 16717 (SQLBUG_65)

SYMPTOMS

An INSERT INTO a global temp table from a stored procedure may result in the following errors during the second and third executions:

At the second execution:

Msg 2714, Level 16, State 1
There is already an object named '%.*s' in the database.

At the third execution:

Msg 267, Level 16, State 1
Object '%.*s' cannot be found.

The following script demonstrates the problem:


   DROP PROCEDURE sp_test
   GO
   CREATE PROCEDURE sp_test
   AS
   EXEC ("IF EXISTS (SELECT * FROM tempdb..sysobjects
      WHERE name = '##testTable' AND type = 'u')
      DROP TABLE ##testTable")
   CREATE TABLE ##testTable (col INT)
   INSERT INTO ##testTable SELECT 1
   cleanup:
   EXEC ("IF EXISTS (SELECT * FROM tempdb..sysobjects
      WHERE name = '##testTable' AND type = 'u') DROP TABLE ##testTable")
   GO

EXEC sp_test /* First execution */


      This command did not return data, and it did not return any rows.

EXEC sp_test /* Second execution */

Msg 2714, Level 16, State 1
There is already an object named '##testTable' in the database.

EXEC sp_test /* Third execution */

Msg 267, Level 16, State 1
Object '' cannot be found.

WORKAROUND

To work around this problem, do either of the following:

Create the stored procedure with the RECOMPILE option.

-or-

Use the EXECUTE command with the INSERT INTO statement.

The following script demonstrates both of the above workarounds:


   CREATE PROCEDURE sp_test WITH RECOMPILE
   AS
   EXEC ("IF EXISTS (SELECT * FROM tempdb..sysobjects
      WHERE name = '##testTable' AND type = 'u')
      DROP TABLE ##testTable")
   CREATE TABLE ##testTable (col INT)
   EXEC ('INSERT INTO ##testTable SELECT 1')
   cleanup:
   EXEC ("IF EXISTS (SELECT * FROM tempdb..sysobjects
      WHERE name = '##testTable' AND type = 'u') DROP TABLE ##testTable")
   GO

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

Additional query words:


Keywords          : kbusage SSrvStProc kbbug6.50 
Version           : winnt:6.5,7.0
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: May 6, 1999