Article ID: 313279
Article Last Modified on 11/21/2006
APPLIES TO
- Microsoft SQL Server 2000 Analysis Services
This article was previously published under Q313279
BUG #: 12631 (plato7x)
SYMPTOMS
When you perform a drill through on a cell in an Analysis Services cube, the drill through operation may fail and you receive one of the following error messages:
Error message 1
Error message 2
Error message 3
Sometimes, when you receive the "Unspecified error" error mesage during drill through, during the the subsequent drill through, you receive the following error message:
The error messages occur when the following conditions are true:
- A member of a parent child dimension defines the cell on which you execute the drill through, other than the ALL member, and the member has approximately 2000, or more, descendants.
- The data source for the cube is a SQL Server database.
In addition to the error message, the following error message appears in the Application Event log on the Analysis Server:
The following error message also appears in the Application Event Log of the SQL Server computer on which the database you use as the data source for the cube is located.
CAUSE
SQL Server limits the number of parameters that you can use in a prepared statement. If you exceed the number of parameters SQL Server allows, the connection terminates. Refer to the "More Information" section for additional details.
RESOLUTION
To resolve this problem, obtain the latest service pack for SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack
STATUS
Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000 Analysis Services. This problem was corrected in Microsoft SQL Server 2000 Service Pack 3.
MORE INFORMATION
When Analysis Services receives a drill through request, it builds a SQL query to request the data from the data source. Analysis Services passes any slices (dimension members) that Analysis Services uses to define the cell on which Analysis Services performs the drill through as part of the WHERE clause in the SQL statement. With non-parent child dimensions, for Analysis Services to pass the slices as part of the WHERE clause, Analysis Services must determine the member key of the current member and then add a join to the dimension table, if necessary. However, for parent-child dimensions, a simple join with the dimension table may not work, due to the recursive nature of parent-child relationships. To resolve the recursive nature of parent-child dimensions, Analysis Services builds an IN clause that contains the member key for the current member and all its descendants in the dimension. Analysis Services then parameterizes the statement and sends the statement to SQL Server.
SQL Server has a 2098 parameter limit for SQL statements. When SQL Server exceeds the parameter limit, SQL Server raises an out of memory error message for the connection and terminates the connection. Analysis Services attempts to read data from the closed SQL connection, and then shuts down when it is unsuccessful.
Microsoft has confirmed this behavior with SQL Server; however, you may see similar behavior for other relational data sources when you work with parent-child dimensions and drill-through operations.
Additional query words: Drill Through drill-through connection lost unexpected fatal error 117 OLAP 17803 qfe cpr hotfix
Keywords: kbfix kbqfe kbbug KB313279
