During the same project the next issue was found: temporary table reuse. Here is an example on “how to reproduce”:
USE tempdb
GO
CREATE PROCEDURE dbo.usp_step_one
AS
BEGIN
CREATE TABLE #temp (ID INT NOT NULL, NUM INT)
ALTER TABLE #temp ADD CONSTRAINT PK_ID PRIMARY KEY (ID)
INSERT INTO #temp SELECT 1, 1
END
GO
CREATE PROCEDURE dbo.usp_step_two
AS
BEGIN
CREATE TABLE #temp (ID INT NOT NULL, NUM INT)
INSERT INTO #temp SELECT 1, 1
END
GO
BEGIN TRAN
EXEC dbo.usp_step_one
PRINT 'step one 1'
EXEC dbo.usp_step_one
PRINT 'step one 2'
EXEC dbo.usp_step_two
PRINT 'step two 1'
COMMIT
GO
DROP PROCEDURE dbo.usp_step_one
DROP PROCEDURE dbo.usp_step_two
GO
SQL 2000 will process this batch without errors, but SQL 2005 will get you the following results:
step one 1
Msg 2714, Level 16, State 4, Procedure usp_step_one, Line 6
There is already an object named 'PK_ID' in the database.
Msg 1750, Level 16, State 0, Procedure usp_step_one, Line 6
Could not create constraint. See previous errors.
The problem is in temporary tables caching during transaction. In SQL 2000 it always used to drop local temp tables as soon as they get out of scope. In SQL 2005, the drop is delayed until the transaction is gone (rollback / commit). You can find some more details about temp tables caching here. As a workaround you can:
- Use table variables instead of temporary tables
- Avoid named constraint creation on temporary objects like
CREATE TABLE #temp (ID INT NOT NULL PRIMARY KEY CLUSTERED, NUM INT)
