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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
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 "Max Degree of Parallelism Option" topic in Microsoft SQL Server 7.0 Books Online.

WORKAROUND

Setting the the Max Degree of Parallelism option to "off" 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 "OPTION (MAXDOP 1)".

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
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbBug kbpending KB259466
©2003 Microsoft Corporation. All rights reserved.