FIX: Plan Reuse Gives Incorrect Query Results (274995)

SYMPTOMS

When an EXEC statement is used to create a local temp table, the plan for that statement may be incorrectly cached and reused, even if subsequent statements create the temp table with different columns.

The following items are characteristic of this problem:

A SELECT INTO is used to create a table within an EXEC statement.
The table created is a local temp table.
The same local temp table name is reused for a different table structure.
The different table structure can include different column names, number of columns, and datatypes.

RESOLUTION

You can work around this problem in the following ways:

Use the same structure every time a temp table of a given name is used.
Do not use the EXEC statement for doing a SELECT INTO for a local temp table.
Use a permanent table or a global temp table.
Issue the following command to clear the cache between executions of the query:
```
DBCC FreeProcCache
```
Note that this will clear all plans out of cache and could have an adverse impact on performance.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

The following example illustrates this behavior:

print '-- local temp tables - cached table structure is incorrectly re-used'
EXEC ('SELECT 1 AS Col1, 2 AS Col2 INTO #temp
UPDATE #temp SET Col2 = 3 
SELECT * from #temp
DROP TABLE #temp')
EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO #temp
UPDATE #temp SET Col2 = 3 
SELECT * from #temp
DROP TABLE #temp')
SELECT 2 AS Col2, 'xyz' AS Col1, 3 AS cC INTO #temp
UPDATE #temp SET Col2 = 3 
SELECT * FROM #temp
DROP TABLE #temp
go

print '-- global temp tables - correct results'
EXEC ('SELECT 1 AS Col1, 2 AS Col2 INTO ##Global
UPDATE ##Global SET Col2 = 3 
SELECT * from ##Global
DROP TABLE ##Global')
EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO ##Global
UPDATE ##Global SET Col2 = 3 
SELECT * from ##Global
DROP TABLE ##Global')
SELECT 2 AS Col2, 1 AS Col1 INTO #Global
UPDATE #Global SET Col2 = 3 
SELECT * FROM #Global
DROP TABLE #Global

print '-- permanent tables - correct results'
EXEC ('SELECT 1 AS Col1, 2 AS Col2 INTO pubs..t1
UPDATE pubs..t1 SET Col2 = 3 
SELECT * from pubs..t1
DROP TABLE pubs..t1')
EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO pubs..t1
UPDATE pubs..t1 SET Col2 = 3 
SELECT * from pubs..t1
DROP TABLE pubs..t1')
SELECT 2 AS Col2, 1 AS Col1 INTO pubs..t1
UPDATE pubs..t1 SET Col2 = 3 
SELECT * FROM pubs..t1
DROP TABLE pubs..t1

print '-- no EXEC involved - correct results'
SELECT 2 AS Col2, 1 AS Col1 INTO #Select
UPDATE #Select SET Col2 = 3 
SELECT * FROM #Select
DROP TABLE #Select
go
SELECT 1 AS Col1, 2 AS Col2 INTO #Select
UPDATE #Select SET Col2 = 3
SELECT * FROM #Select
DROP TABLE #Select
go
dbcc freeproccache

Additional constraints on the observed behavior are:

The data is not retained between statements, only the table structure.
It does not matter if the subsequent executions are from the same connection or a different connection.