Microsoft KB Archive/234482

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

Article ID: 234482

Article Last Modified on 9/22/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q234482



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.



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.



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

Client
  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 </li> Set a Project reference for the ADO Object Library.</li> Set a reference to the ActiveX ADOBusObj created in step 3 of the preceding Server section.</li> Run the Client application and you will see the error message.</li></ol>

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.

<div class="references_section">