Microsoft KB Archive/308757

= FIX: Select from Nested Views May Not Return Result After SQL Server Updates Statistics =

Article ID: 308757

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308757



BUG #: 355066 (SHILOH_BUGS)



SYMPTOMS
A SELECT from some nested views may not return results after SQL Server updates statistical information.

For example: SELECT * FROM NestedView



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack



WORKAROUND
To work around this problem you can either:
 * Remove the GROUP BY clause from the view if you use a GROUP BY. For example, remove the GROUP BY clause in the CommonView.

-or-


 * Create multiple copies of the same view. Each outer view uses a different inner view. For example, ViewA uses CommonView, ViewB uses CommonView1 and so forth.

NOTE: CommonView and CommondView1 have the same view definitions. Refer to the &quot;More Information&quot; section of this article for view definitions.



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

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.



Definition of the Views
Here is a code sample that defines the views: CREATE VIEW NestedView AS SELECT ViewA.ColA, ViewB.ColB, FROM Table1 LEFT OUTER JOIN ViewA ON    Table1.Col1 = ViewA.Col1 AND Table1.Col2 = ViewA.Col1 LEFT OUTER JOIN ViewB ON    Table1.Col1 = ViewB.Col1 AND Table1.Col2 = ViewB.Col2

CREATE VIEW ViewA AS SELECT  FROM CommonView WHERE  GROUP BY 

CREATE VIEW ViewB AS SELECT  FROM CommonView WHERE  GROUP BY 

CREATE VIEW CommonView AS SELECT  FROM < INNER_JOINs > GROUP BY  HAVING  <BR/>

Additional query words: update statistics sp_updatestats

Keywords: kbbug kbfix KB308757

-

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

© Microsoft Corporation. All rights reserved.