Article ID: 248441
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q248441
BUG #: 54744 (SQLBUG_70)
SYMPTOMS
An INSERT statement in a table from a subquery on the same table gives incorrect results if either of the following sets of conditions is true:
- The DISTINCT keyword is used in the subquery.
-and-
- No index exists on the table.
-or-
- The TOP and ORDER BY keywords are used in the subquery.
-and-
- An index exists on the table.
CAUSE
This is a Halloween problem when reading data just inserted in the same query. Normally, it is not possible to insert values in a table based on the previous row inserted in the same query. Halloween protection is needed to prevent a situation where the physical location of a row within a table changes due to an UPDATE operation. As a result, the same row may be revisited multiple times within the context of a single logical operation, which should not occur. If the query table and query does not have the previous condition, there is no bug.
WORKAROUND
To work around this problem, do not attempt to insert rows based on previous rows inserted in the same query.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
For more information, contact your primary support provider.
Keywords: kbbug kbfix KB248441
