Article ID: 269282
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 7.0 Service Pack 2
This article was previously published under Q269282
BUG #: 58199 (SQLBUG_70)
SYMPTOMS
The following error message might occur when you use a non-clustered index for a select query on a temporary table:
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:
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.
Additional query words: Error Message 605
Keywords: kbbug kbfix KB269282
