Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

This article was previously published under Q234482

↑ Back to the top


Symptoms

If you try to use Properties in a SQL statement with an out-of-process ADODB component, the Microsoft OLAP provider results in one of the following error messages:
(-2147467259) "The data provider or other service returned an E_FAIL status."
-or-
-2147217891 (80040e1d) Method 'GetRs' of object '_objRS' failed.
This works fine in-process.

↑ Back to the top


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.

↑ Back to the top


More information

Steps to Reproduce the Behavior

Use the following steps to reproduce the problem.

NOTE: This code sample requires the Microsoft OLAP OLEDB provider on the SQL Server computer with the FoodMart OLAP database. The Microsoft OLAP OLEDB provider is installed when you install the OLAP client components from the SQL Server 7.0 CD.

Server

  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 Microsoft ActiveX Data Objects Library.
  3. Change the name of the Project to ADOBusObj and then change the name of the class to objRs.
  4. Compile the application.

Client

  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.
If you uncomment the second SQL statement and comment out the first SQL statement in the preceding Client section, you will not get an error. To work around this problem use ADOMD instead of ADODB with Microsoft OLAP for Out of Process business objects.

↑ Back to the top


References

SQL Server Books Online; topic: "ADOMD"
231951� INF: Permissions That You Must Have to Administer an OLAP Server
199002� INF: Example Active Server Page to Access OLAP Services

↑ Back to the top


Keywords: kbdatabase, kbprb, KB234482

↑ Back to the top

Article Info
Article ID : 234482
Revision : 5
Created on : 9/22/2003
Published on : 9/22/2003
Exists online : False
Views : 227