Microsoft KB Archive/166200

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

Article ID: 166200

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q166200



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 6.5.

Keywords: kbbug kbpending kbusage KB166200

-

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

© Microsoft Corporation. All rights reserved.