FIX: Correlated Subquery in Select List w/ ANSI JOIN Causes AV

 Last reviewed: June 27, 1997
Article ID: Q164293
The information in this article applies to:
  • Microsoft SQL Server, version 6.5 Service Pack 2
BUG #: 16586 (Windows NT: 6.5)

SYMPTOMS

A query using a correlated subquery in the select list combined with an ANSI JOIN may cause a handled access violation (AV). The client connection will be terminated when the query is run.

WORKAROUND

To work around this problem, change the query to avoid using the non-ANSI syntax (that is, the = operator) for the join in the subselect.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5 Service Pack 2. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The following is an example query that results in the handled access violation: 

   SELECT (SELECT count(*) FROM master_table
   JOIN Status ON master_table.ID = Status.ID
   AND Status.Code = 'COMPLETE'
   WHERE  target_table.ID = master_table.ID
   ) FROM   target_table
   go

Keywords : kbbug6.50.sp2 kbusage SSrvTran_SQL kbbuglist
Version : 6.5 SP2
Platform : WINDOWS
Issue type : kbbug
Resolution Type : kbfix

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 27, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.