Microsoft KB Archive/259466

= BUG: SELECT Statement with a Correlated Subquery on a View with a Substring Causes an Access Violation on a Multi-Proc Computer =

Article ID: 259466

Article Last Modified on 10/17/2003

-

APPLIES TO


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

-



This article was previously published under Q259466



BUG #: 228475 (Shiloh_bugs)

BUG #: 57830 (SQLBUG_70)



SYMPTOMS
Running a SELECT statement with a correlated subquery, on a view that contains a Substring function, causes an Access Violation (AV) to occur.

NOTE: The Access Violation only occurs when the plan that is run involves parallelism.

For more information, see the &quot;Max Degree of Parallelism Option&quot; topic in Microsoft SQL Server 7.0 Books Online.



WORKAROUND
Setting the the Max Degree of Parallelism option to &quot;off&quot; prevents the Access Violation.

You can set this value either at the:
 * Server level by using the sp_configure option.

-or-


 * Query level by using the hint &quot;OPTION (MAXDOP 1)&quot;.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
The following example demonstrates the type of query that could cause this access violation. This example is only for illustration purposes and actually requires data for it to produce the exception. CREATE TABLE [dbo].[AccountHistory] (   [acct] [char] (10) NOT NULL,    [ytdbal00] [float] NOT NULL ,    [sub] [char] (24) NOT NULL ) ON [PRIMARY] GO

CREATE VIEW dbo.ViewAcctHist AS SELECT AH.Acct, SUBSTRING(AH.Sub, 1, 4) AS DepartmentCode, AH.YtdBal00 FROM AccountHistory AH GO

SELECT BRB.DepartmentCode, BRB.Acct, (SELECT Sum(Base.YTDBal00) FROM dbo.ViewAcctHist Base WHERE Base.Acct=BRB.Acct AND Base.DepartmentCode=BRB.DepartmentCode) as YTDActual FROM dbo.ViewAcctHist BRB GROUP BY BRB.DepartmentCode, BRB.Acct

Keywords: kbbug kbpending KB259466

-

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

© Microsoft Corporation. All rights reserved.