Microsoft KB Archive/313279

= FIX: Unable to drill through cell in an Analysis Services cube and connection to server terminates =

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

Unable to drill through.

Connection to the server is lost.

Error message 2

Unable to drill through.

An error occurred during query execution

Error message 3

Unable to drill through.

Data source provider error. Unspecified error

Sometimes, when you receive the &quot;Unspecified error&quot; error mesage during drill through, during the the subsequent drill through, you receive the following error message:

Data source provider error. Multiple-stop OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

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:

Event Type: Error

Event Source: MSSQLServerOLAPService

Event Category: Server

Event ID: 117

Date: 11/21/2001

Time: 10:44:38 AM

User: N/A

Computer: OLAPServer

Description:

Unexpected fatal error occurred. Attempting to restart 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.

Event Type: Error

Event Source: MSSQLSERVER

Event Category: (2)

Event ID: 17052

Date: 11/21/2001

Time: 10:44:38 AM

User: MyDomain\OLAPService

Computer: SQLServer

Description:

Error: 17803, Severity: 20, State: 12

Insufficient memory available.



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 &quot;More Information&quot; 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

-

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

© Microsoft Corporation. All rights reserved.