PRB: ADODB Out of Process Error with Microsoft OLAP using Properties in SQL Statement

• Microsoft Data Access Components versions 2.1 (GA), 2.1 SP1, 2.1 SP2
• ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2

SYMPTOMS

Attempting to use Properties in a SQL statement with an Out of Process ADODB component and the Microsoft OLAP provider results in this error:

(-2147467259) "The data provider or other service returned an E_FAIL status."

CAUSE

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

RESOLUTION

If you need to use Properties in a SQL statement with an Out of Process component you must use ActiveX Data Objects (Multi-dimensional)(ADOMD) instead of ADODB with the Microsoft OLAP provider. For an example of using ADOMD as a work around, see the References section of this article.

MORE INFORMATION

Steps to Reproduce the Behavior

1. Create a new Visual Basic ActiveX EXE Project and paste the following code in the Class:

   
   Option Explicit
   Private strSQL As String
   Private strConnect As String
   Private adoCn As ADODB.Connection
   
   Public Function GetRs() As ADODB.Recordset
       If Not adoCn Is Nothing Then
       Else
           Err.Raise vbObjectError + 98, "GetRs", "No valid Connection"
       End If
   
       Dim adoRs As ADODB.Recordset
   
       Set adoRs = New ADODB.Recordset
       With adoRs
           .CursorLocation = adUseClient
           .ActiveConnection = adoCn
           .CursorType = adOpenStatic
           .LockType = adLockBatchOptimistic
           .Open strSQL
       End With
   
       'disConnect the Recordset.
       Set adoRs.ActiveConnection = Nothing
   
       'return the Recordset
       Set GetRs = adoRs
   End Function
   
   Private Property Get ConnectStr() As String
       ConnectStr = strConnect
   End Property
   
   Private Property Let ConnectStr(strCn As String)
       strConnect = strCn
   End Property
   
   Public Property Get SQL() As String
       SQL = strSQL
   End Property
   
   Public Property Let SQL(nSQL As String)
       strSQL = nSQL
   End Property
   
   Public Sub ADOConnect(strConnect As String, Optional CmdTimeOut As Integer = 20)
       Set adoCn = New ADODB.Connection
       With adoCn
           .ConnectionString = strConnect
           .CursorLocation = adUseClient
           .CommandTimeout = CmdTimeOut
           .Open
       End With
   
       ConnectStr = adoCn
   End Sub 




2. Set a Project reference for the ADO 2.1 Object Library.



3. Change the name of the Project to ADOBusObj and then change the name of the class to objRs.



4. Compile the application.

1. Create a new Visual Basic Standard EXE Project and paste the following code in the Form General Declarations section:

   
   Option Explicit
   Const strConnect = "Data Source=<DataSource>;PROVIDER=MSOLAP;INITIAL CATALOG=FoodMart"
   
   Private Sub Form_Click()
       On Error GoTo ErrorHandler
   
       Dim adoRs As ADODB.Recordset
       Dim objAdoData As New ADOBusObj.objRs
   
       With objAdoData
           'this works in or out of process.
           '.SQL = "select {[Measures].[Unit Sales]} on columns, " & _
                    "Non Empty [Store].[Store Name].members " & _
                    "on rows From Sales"
            'this works in process but fails out of process.
           .SQL = "select {[Measures].[Unit Sales]} on columns, " & _
                    "Non Empty [Store].[Store Name].members " & _
                    "Properties [Store].[Store Type], " & _
                    "[Store].[Store Manager] on rows From Sales"
           .ADOConnect strConnect, 20 'Establish connection.
       End With
       
       'Return the Resultset from Data Object.
       Set adoRs = objAdoData.GetRs
       
       Debug.Print adoRs.RecordCount
       
       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 




2. Set a Project reference for the ADO Object Library.



3. Set a reference to the ActiveX ADOBusObj created in step 3 of the preceding Server section.



4. Run the Client application and you will see the error message.

REFERENCES

SQL Server Books Online; topic: "ADOMD"

Q231951 INF: Permission Needed for Administering an OLAP Server

Q199002 INF: Example Active Server Page to Access OLAP Services

Additional query words:

Keywords : kbADO kbDatabase kbOLEDB kbSQLServ kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:2.1,2.1 (GA),2.1 SP1,2.1 SP2
Platform : WINDOWS
Issue type : kbprb