FIX: SP2 Regression: Running a Stored Procedure with Index on Local Temp Table Causes AV (276499)

SYMPTOMS

When running a stored procedure that contains indexed local temporary tables, and SELECT or UPDATE statements on the tables, you may get an exception access violation (AV). The connection may seem to stop responding (hang), and may eventually time out with an error that resembles the following:

Time out occurred while waiting for buffer latch type 1, bp 0x117a6900,
page (0:0), stat 0x40d, object ID 2:611966976:0, waittime 500. Continuing to wait.

You may also receive one of the following errors:

Error: 602, Severity: 21, State: 13
Could not find row in sysindexes for database ID 2, object ID 181575685, index ID 1. Run DBCC CHECKTABLE on sysindexes..

Error: 602, Severity: 21, State: 13
Could not find row in sysindexes for database ID 2, object ID 213575799, index ID 1. Run DBCC CHECKTABLE on sysindexes..

-or-

Error: 5180, Severity: 22, State: 1
2000-09-30 15:54:24.79 spid8 Could not open FCB for invalid file ID 0 in database 'tempdb'. Table or database may be corrupted..

CAUSE

SQL Server caches index IDs in the compiled plan. However, the stored procedure can be such that for a different invocation this cached index ID belongs to a different index on the local temporary table. A check for sharing and reusing plans does not seem to catch this, and SQL Server uses the old plan for this invocation. In addition, auto-create and auto-drop statistics that are used in the query plan do not keep track of the temporary table, which results in the errors.

For details on related bug #57917, see the following article:

275721 FIX: Execution of Stored Procedure with Local Temp Table and Indexes May Fail or Return Incorrect Results

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in SQL Server 7.0 Service Pack 3. For more information, contact your primary support provider.

RESOLUTION

To resolve this problem, obtain the latest service pack for SQL Server 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

301511 INF: How to Obtain the Latest SQL Server 7.0 Service Pack

WORKAROUND

To work around this problem:

Execute the procedure using the WITH RECOMPILE option.
-or-
Avoid creating an index on the temp table in the procedure.