Microsoft KB Archive/265263

= PRB: Cannot Open a Client-Side Cursor for a SQL Query to OLAP Server =

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, &quot;Select * from Sales&quot;) 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, &quot;Select * from Sales&quot;) 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 = &quot;Data Source=SyedY3;PROVIDER=MSOLAP;INITIAL CATALOG=FoodMart 2000&quot; '  Const strSQL = &quot;Select {[Measures].[Unit Sales]} on columns, &quot; & _ '                     &quot;Non Empty [Store].[Store Name].members on rows From Sales&quot; Const strSQL = &quot;Select * from Sales&quot;

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 &quot;Success&quot;, vbOKOnly, &quot;Data Object&quot; Exit Sub ErrorHandler: MsgBox &quot;Change Failed:&quot; & vbCrLf & Err.Number & vbCrLf & Err.Description, vbOKOnly, &quot;Data Object&quot; 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.