question

David Hill avatar image
David Hill asked

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

sql-server-2008sqlssaslinked-servermdx
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Rob Farley avatar image
Rob Farley answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Hill avatar image David Hill commented ·
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 !
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.