FIX: Plan Reuse Gives Incorrect Query Results

This article was previously published under Q274995
This article has been archived. It is offered "as is" and will no longer be updated.
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 #tempUPDATE #temp SET Col2 = 3 SELECT * from #tempDROP TABLE #temp')EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO #tempUPDATE #temp SET Col2 = 3 SELECT * from #tempDROP TABLE #temp')SELECT 2 AS Col2, 'xyz' AS Col1, 3 AS cC INTO #tempUPDATE #temp SET Col2 = 3 SELECT * FROM #tempDROP TABLE #tempgoprint '-- global temp tables - correct results'EXEC ('SELECT 1 AS Col1, 2 AS Col2 INTO ##GlobalUPDATE ##Global SET Col2 = 3 SELECT * from ##GlobalDROP TABLE ##Global')EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO ##GlobalUPDATE ##Global SET Col2 = 3 SELECT * from ##GlobalDROP TABLE ##Global')SELECT 2 AS Col2, 1 AS Col1 INTO #GlobalUPDATE #Global SET Col2 = 3 SELECT * FROM #GlobalDROP TABLE #Globalprint '-- permanent tables - correct results'EXEC ('SELECT 1 AS Col1, 2 AS Col2 INTO pubs..t1UPDATE pubs..t1 SET Col2 = 3 SELECT * from pubs..t1DROP TABLE pubs..t1')EXEC ('SELECT 2 AS Col2, 1 AS Col1 INTO pubs..t1UPDATE pubs..t1 SET Col2 = 3 SELECT * from pubs..t1DROP TABLE pubs..t1')SELECT 2 AS Col2, 1 AS Col1 INTO pubs..t1UPDATE pubs..t1 SET Col2 = 3 SELECT * FROM pubs..t1DROP TABLE pubs..t1print '-- no EXEC involved - correct results'SELECT 2 AS Col2, 1 AS Col1 INTO #SelectUPDATE #Select SET Col2 = 3 SELECT * FROM #SelectDROP TABLE #SelectgoSELECT 1 AS Col1, 2 AS Col2 INTO #SelectUPDATE #Select SET Col2 = 3SELECT * FROM #SelectDROP TABLE #Selectgodbcc 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.
Properties

Article ID: 274995 - Last Review: 11/02/2013 03:02:00 - Revision: 3.0

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix KB274995
Feedback