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 

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


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, 2010 at 05:46 PM in Default

avatar image

David Hill
11 1 1 3

(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, 2010 at 09:42 PM

avatar image

Rob Farley
5.8k 16 22 28

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, 2010 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 28, 2010 at 05:46 PM

Seen: 6759 times

Last Updated: Jan 28, 2010 at 09:40 PM

Copyright 2018 Redgate Software. Privacy Policy