Microsoft KB Archive/274995

= FIX: Plan Reuse Gives Incorrect Query Results =

Article ID: 274995

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q274995



Bug: 57746 (sqlbug_70)



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.

Keywords: kbbug kbfix KB274995

-

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

© Microsoft Corporation. All rights reserved.