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 2Unable to drill through.
An error occurred
during query execution Error message 3Unable 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
STATUSMicrosoft 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: | Major | Last Reviewed: | 1/26/2006 |
|---|
| Keywords: | kbfix kbQFE kbbug KB313279 kbAudDeveloper |
|---|
|