Microsoft KB Archive/275235

= PRB: SQL SELECT Query to OLAP Does Not Return Results for Certain WHERE Clauses =

Article ID: 275235

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server OLAP Services

-



This article was previously published under Q275235



BUG #: 9744 (Plato7x)



SYMPTOMS
A SQL SELECT query to a Microsoft OLAP 7.0 Server, using the Microsoft OLE DB Provider for OLAP, might not return results when the number of members is greater than the large level threshold and the unique member name is not specified.

For example, if you query against the OLAP 7.0 FoodMart Sales database and you specify a WHERE clause with the Customers:Name level, a result set may not be produced for some queries.



CAUSE
The Customers:Name level has 10281 members, which is greater than the default setting for the large level threshold property, and this means it is a virtual level.



RESOLUTION
Use a unique name in the WHERE clause of the Multidimensional Expression (MDX) query: SELECT DISTINCT [Customers:Country], [Customers:State Province], [Customers:City], [Customers:Name] FROM [Sales] WHERE [Customers:Name] = '[Customers].[All Customers].[USA].[WA].[Bremerton].[Larry Weeks]'



STATUS
This behavior is by design in SQL Server OLAP version 7.0.

The problem does not occur in Microsoft SQL Server 2000 Analysis Services.



MORE INFORMATION
You can use an ad-hoc query tool such as the OLE DB QueryDemo sample or a Microsoft ActiveX Data Objects (ADO) application to pass a SQL query to the OLAP server and get the results.

Here are a few examples:

Case 1

SELECT DISTINCT [Customers:Country], [Customers:State Province], [Customers:City], [Customers:Name] FROM [Sales] WHERE [Customers:City] = 'Bremerton' The preceding SELECT statement returns 90 rows with the first row Customers:Name of Larry Weeks

Case 2 SELECT DISTINCT [Customers:Country], [Customers:State Province], [Customers:City], [Customers:Name] FROM [Sales] WHERE [Customers:City] = 'Bremerton' AND [Customers:Name] = 'Larry Weeks' The preceding SELECT statement returns 1 row with Customers:Name of Larry Weeks

Case 3 SELECT DISTINCT [Customers:Country], [Customers:State Province], [Customers:City], [Customers:Name] FROM [Sales] WHERE [Customers:Name] = 'Larry Weeks' The preceding SELECT statement returns 0 rows instead of 1 row like the preceding case 2.

Keywords: kbprb KB275235

-

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

© Microsoft Corporation. All rights reserved.