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

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: -or- This works fine in-process.

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.

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.

SQL Server Books Online; topic: "ADOMD"