DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 248441 - Last Review: November 2, 2013 - Revision: 3.0

This article was previously published under Q248441
This article has been archived. It is offered "as is" and will no longer be updated.
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  (http://support.microsoft.com/kb/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.

Applies to
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB248441
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support