FIX: Error Message 602 Occurs When You Query a Temporary Table That Has a Non-Clustered Index (269282)

SYMPTOMS

The following error message might occur when you use a non-clustered index for a select query on a temporary table:

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

This problem only occurs with Microsoft SQL Server 7.0 Service Pack 2 (SP2) (build 7.00.842). This is a Service Pack 2 regression issue.

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

Steps to Reproduce Behavior

Create a stored procedure that does the following:

Creates a temporary table:
```
create table #tab1 (Col1 int)
```

Inserts 2 to 3 records into the temporary table.

insert into #tab1 values (1)
go
insert into #tab1 values (1)
go

Creates a non-clustered index on the temporary table.
```
create index ind1 on #tab1(col1)
go
```
Performs a select on the temporary table, which has a join that includes a non-temporary table. This step needs to occur in a stress condition. The problem is almost never reproducible if you use the Query Analyzer.
```
select * from #tab1 (index = ind)
```

When you run the preceding procedure, under a stress environment, error message 602 is seen in the Errorlog. As a result, the server process ID (SPID) that is causing the error is terminated.

Here is a list of the symptoms that you see after the error is reported in the Errorlog:

The SPID that is causing the error is terminated.
If you run the DBCC CHECKDB statement on the tempdb database, the DBCC CHECKDB returns clean. There are no errors reported regarding this problem in a DBCC check.
You do not need to restart the SQL Server service to clear the tempdb database. This problem is only at the connection level and is corrected when the connection is terminated.