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

Article translations Article translations
Article ID: 269282 - View products that this article applies to.
This article was previously published under Q269282
BUG #: 58199 (SQLBUG_70)
Expand all | Collapse all

On This Page

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.

WORKAROUND

Use either of the following two methods to work around this problem:
  • Change the non-clustered index that you created on the temporary table to a clustered index.

    -or-

  • Do not use the non-clustered index on a temporary table.

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:
  1. Creates a temporary table:
    create table #tab1 (Col1 int)
  2. Inserts 2 to 3 records into the temporary table.
    insert into #tab1 values (1)
    go
    insert into #tab1 values (1)
    go
  3. Creates a non-clustered index on the temporary table.
    create index ind1 on #tab1(col1)
    go
  4. 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.

Properties

Article ID: 269282 - Last Review: March 14, 2006 - Revision: 4.1
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 7.0 Service Pack 2
Keywords: 
kbbug kbfix KB269282

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com