Microsoft KB Archive/265263

From BetaArchive Wiki

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:

Change Failed
-2147217887
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.

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:

Change Failed
-2147217887
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.

Keywords: kbprb KB265263