Article ID: 265263
Article Last Modified on 11/6/2003
APPLIES TO
- Microsoft SQL Server OLAP Services
This article was previously published under Q265263
SYMPTOMS
Using ActiveX Data Objects (ADO), if you pass an MDX query to the SQL Server 2000 Analysis Services (OLAP) server, you can open a recordset by using either a client-side or server-side cursor. However, if you pass a SQL query (for example, "Select * from Sales") to OLAP server, you cannot open a client-side cursor. The following error occurs at adoRs.Open:
CAUSE
This behavior is by design.
WORKAROUND
The following are three separate workarounds:
- Implement Sorting routing for the recordset in your application (by using additional arrays after you receive the recordset).
- Redesign the application to generate an MDX query instead of a SQL query.
- Use ADO to point to relational data and obtain the result set directly. ORDER BY will be available and you can also use client-side cursor.
MORE INFORMATION
Using ADO, if you pass an MDX query to OLAP server, you can open a recordset by using either a client-side or server-side cursor. However, if you pass a SQL query (for example, "Select * from Sales") to OLAP server, you cannot open a client-side cursor.
You may need a client-side cursor to use ADO's Sort property, which is not available with Server cursors. Note that you may need to sort the resultset on client-side because ORDER BY is not supported for SQL Syntax to OLAP Server.
Steps To Reproduce the Problem:
Paste the following code into a new Visual Basic Standard EXE Project Form in the General Declarations section. Set a reference to the ADO 2.1 Object Library.
Option Explicit Const strConnect = "Data Source=SyedY3;PROVIDER=MSOLAP;INITIAL CATALOG=FoodMart 2000" ' Const strSQL = "Select {[Measures].[Unit Sales]} on columns, " & _ ' "Non Empty [Store].[Store Name].members on rows From Sales" Const strSQL = "Select * from Sales" Private Sub Form_Click() On Error GoTo ErrorHandler Dim adoCn As ADODB.Connection Set adoCn = New ADODB.Connection With adoCn .ConnectionString = strConnect .CursorLocation = adUseClient '.CursorLocation = adUseServer .CommandTimeout = 20 .Open End With Dim adoRs As ADODB.Recordset Set adoRs = New ADODB.Recordset With adoRs .CursorLocation = adUseClient '.CursorLocation = adUseServer .ActiveConnection = adoCn .CursorType = adOpenStatic .LockType = adLockBatchOptimistic .Open strSQL End With While Not adoRs.EOF Debug.Print adoRs.Fields(0).Value adoRs.MoveNext Wend MsgBox "Success", vbOKOnly, "Data Object" Exit Sub ErrorHandler: MsgBox "Change Failed:" & vbCrLf & Err.Number & vbCrLf & Err.Description, vbOKOnly, "Data Object" Exit Sub End Sub
The following error occurs at adoRs.Open:
Keywords: kbprb KB265263