Microsoft KB Archive/235693

= FIX: Select Query with GROUP BY and Outer Join Causes Handled Access Violation =

Article ID: 235693

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q235693



BUG #: 55981 (SQLBUG_70)



SYMPTOMS
A handled access violation (AV) exception error occurs within a SELECT statement under the following conditions:
 * There is an outer join and the outer table has a nonclustered unique index on the column in the JOIN condition.
 * The SELECT statement contains a GROUP BY clause with two or more columns.
 * A number of the columns from both tables are used in the search conditions of the WHERE clause.
 * The execution plan does not cause a sort or ORDER BY.

The following is the short stack trace for the exception: Short Stack Dump 0x006bdcab Module(sqlservr+2bdcab) (SDES::RepositionScan+39) 0x005911db Module(sqlservr+1911db) (RowsetSS::ReaquireLatchLong+b4) 0x00421775 Module(sqlservr+21775) (RowsetSS::GetData+42) 0x004ac022 Module(sqlservr+ac022) (CValSSTable::GetDataX+1c) 0x00630cc8 Module(sqlservr+230cc8) (CEs::FastCompare+7e) 0x00426876 Module(sqlservr+26876) (CDataBuffer::FGetPredicate+3a) 1999-06-23 12:20:31.87 spid9   CImageHelper::GetSym Error - The specified module could not be found.



WORKAROUND
The problem occurs when the execution plan performs a Hash Match without any sorting or ordering. The workaround is to apply either query hints or join hints that will create an execution plan that performs a sort or an order.
 * Use the 'order group' query hint.

-or-
 * Use the 'merge', 'loop', or 'hash' join hint.



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. Microsoft has confirmed this to be a problem in SQL Server 2000.

Keywords: kbbug kbfix KB235693

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.