Hi,
I have Sql / Analysis Server 2005 installed on server, i'm trying to run MDX
query from sql server using OpenQuery or OpenRowSet function, i setup the
Linked server

/* Add new linked server */
EXEC sp_addlinkedserver
@server='LINKED_OLAP', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='VNUMRKCENS01', -- analysis server name (machine name)
@catalog='LBL2' -- default catalog/database
GO

and then run mdx query like:
SELECT *
FROM OPENQUERY(LINKED_OLAP,'select { [Measures].[Dollars],
[Measures].[Units]} on columns from [Census]')

on runing this query get following error msg:
Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'MSOLAP'.
OLE DB error trace [Non-interface error: CoCreate of DSO for MSOLAP
returned 0x80040154].

Any clue, please answer in detail.
Regards - Hasan

sp_addlinkedserver does not set the allow-inprocess setting on the
provider, which the MSOLAP provider requires in order to work.

Open up SSMS and from your server navigate down to

Server Objects > Linked Servers > Providers

Double click on the MSOLAP provider and in the properties window that
appears, turn on the setting for "Allow inprocess". I think this setting
will take effect immediately, if not, you might need to stop and start
the SQL Server service.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <B1000CE2-581B-401F-BB3B-3A32167D8534 (AT) microsoft (DOT) com>,
Hasan (AT) discussions (DOT) microsoft.com says...

Hi Darren,
I've tried it, its not resolving the issue, Allow process is already
enabled, i'm using following statements:


sp_addlinkedserver @server = 'LinkOLAP', @srvproduct = 'Analysis Services
2000',
@provider = 'MSOLAP', @datasrc = 'vnumrkcens01', @catalog ='LBL'

go

SELECT * FROM openquery(LinkOLAP,'select { [Measures].[Dollars],
[Measures].[Units]} on columns from [Census]')

I have Windows Xp and Sql / Analysis Server 2005, MDX query is working fine
in MDX query analyser, not working with OpenQuery or OpenRowSet function.
Waiting for earliest response.
regards - Hasan

"Darren Gosbell" wrote:

If you can run queries OK from Management Studio (which I assume is what
you mean by MDX Query Analyser) this means that the provider and
Analysis Services must be working correctly and that your query is
valid. If the linked server provider is set to Allow Inprocess the other
thing that it could be is a security issue.

Because SSAS uses windows authentication, your SQL Server will need to
be running under a user account that has access to the cubes. If you are
running a default install, SQL is probably running as one of the service
account (either local system or network service).

If you change the identity of the SQL Server service account, make sure
you use the SQL Configuration Manager to do this. The Configuration
Manager makes sure that the account you use has all the appropriate
extra priviledges set. If you change the account via other means things
might not work correctly.

--
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell

*** Sent via Developersdex http://www.developersdex.com ***