PRB: Cannot Open a Client-Side Cursor for a SQL Query to OLAP Server (265263)

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: