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



The information in this article 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 "Unspecified error" 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 "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.

Modification Type:MajorLast Reviewed:1/26/2006
Keywords:kbfix kbQFE kbbug KB313279 kbAudDeveloper