SYMPTOMS
An INSERT INTO a global temp table from a stored procedure may result in
the following errors during the second and third executions:
Msg 2714, Level 16, State 1
There is already an object named '%.*s' in the database.
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