x

MDX Query against Linked Server (SSAS)

Hi, I am trying to execute an MDX query against a SQL 2008 cube, by running a passthrough query using OPENQUERY function.

The query executes fine when running it in a MDX query window

SELECT {[Measures].[PersonCount]} on COLUMNS,
       {[DimDate].[CalendarYear].Members} on ROWS 
FROM [OLAPDemo]

But when running it against the linked server via OPENQUERY i receive the following error

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP.4" for linked server "LINKED_OLAP" reported an error. 
   The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSOLAP.4" for linked server "LINKED_OLAP".

Here is the code i used to set up the linked server, and the actual passthrough query

EXEC sp_addlinkedserver 
@server='LINKED_OLAP', -- local SQL name given to the linked server
@srvproduct='', -- not used 
@provider='MSOLAP.4', -- OLE DB provider (the .4 means the SQL2K8 version) 
@datasrc='localhost', -- analysis server name (machine name) 
@catalog='OLAPDemo' -- default catalog/database

GO

SELECT * 
FROM OpenQuery(LINKED_OLAP,
       'SELECT {[Measures].[PersonCount]} on COLUMNS,
               {[DimDate].[CalendarYear].Members} on ROWS 
        FROM [OLAPDemo]
       ')

Any help much appreciated

more ▼

asked Jan 28 '10 at 05:46 PM in Default

David Hill gravatar image

David Hill
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I think this is the one that is solved by setting the Provider option for Inprocess.

In SSMS, go to Server Objects, Linked Servers, Providers, MSOLAP, Properties, and you should see "Allow inprocess" in the list of Provider options. Tick "Enable", and see how you go.

Like I said... I have a feeling that this is the one.

more ▼

answered Jan 28 '10 at 09:42 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

I checked the setting above, and it was already set. So i switched it off, then on, and changed my provider param for the linked server setup to "MSOLAP" rather than "MSOLAP.4", and the query now works. Thanks for the pointer !
Feb 01 '10 at 08:20 AM David Hill
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x672
x166
x101
x62

asked: Jan 28 '10 at 05:46 PM

Seen: 4740 times

Last Updated: Jan 28 '10 at 09:40 PM