MS SQL Server OLAP - Linked Server to Analysis Services 2005

Author

Linked Server to Analysis Services 2005

See Lim

2005-11-22, 8:24 pm

Hi,

I'm having problem creating a Linked Server to Adventure Works cube in
Analysis Services 2005.
Below is my sql to create the linked server.

EXEC sp_addlinkedserver
@server='AdventureWo
rksDW',
@srvproduct='',
@provider='MSOLAP',
@datasrc='Localhost\
SQLSERVER2005',
@catalog='Adventure Works DW'

After creating the linked server I ran

EXEC sp_testlinkedserver N'AdventureWorksDW'

but got this error

Msg 7399, Level 16, State 1, Procedure sp_testlinkedserver,
Line 1
The OLE DB provider "MSOLAP" for linked server "AdventureWorksDW" reported
an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver,
Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for
linked server "AdventureWorksDW".

Anyone any idea what I'm doing wrong here?
Thanks in advance.

Raman Iyer [MS]

2005-11-22, 8:24 pm

Can you make sure that you have allowed inproc access in SQL Server for the
MSOLAP provider?:
1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
to Server Objects->Linked Server->Providers in the Object Explorer.
2.. Right-click on MSOLAP and select Properties. Check the "Allow
inprocess" box in the Provider Options dialog that comes up
--
-Raman Iyer
SQL Server Data Mining
[This posting is provided "AS IS" with no warranties, and confers no
rights.]

"See Lim" <seel@symmetrics.net> wrote in message
news:u$6Yfa67FHA.1188@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I'm having problem creating a Linked Server to Adventure Works cube in
> Analysis Services 2005.
> Below is my sql to create the linked server.
>
> EXEC sp_addlinkedserver
> @server='AdventureWo
rksDW',
> @srvproduct='',
> @provider='MSOLAP',
> @datasrc='Localhost\
SQLSERVER2005',
> @catalog='Adventure Works DW'
>
> After creating the linked server I ran
>
> EXEC sp_testlinkedserver N'AdventureWorksDW'
>
> but got this error
>
> Msg 7399, Level 16, State 1, Procedure sp_testlinkedserver,
Line 1
> The OLE DB provider "MSOLAP" for linked server "AdventureWorksDW" reported
> an error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver,
Line 1
> Cannot initialize the data source object of OLE DB provider "MSOLAP" for
> linked server "AdventureWorksDW".
>
> Anyone any idea what I'm doing wrong here?
> Thanks in advance.
>
>
>
>
>

See Lim

2005-11-22, 8:24 pm

It still doesn't work. However, I have no problem creating a linked server
to Analysis Services 2000 FoodMart and running the sp_testlinkedserver. from
SQL Server 2005

"Raman Iyer [MS]" <ramaniy@online.microsoft.com> wrote in message
news:u2AyI667FHA.2384@TK2MSFTNGP12.phx.gbl...
> Can you make sure that you have allowed inproc access in SQL Server for
> the MSOLAP provider?:
> 1.. In SQL Mgmt Studio, connect to the SQL Server Database Engine and go
> to Server Objects->Linked Server->Providers in the Object Explorer.
> 2.. Right-click on MSOLAP and select Properties. Check the "Allow
> inprocess" box in the Provider Options dialog that comes up
> --
> -Raman Iyer
> SQL Server Data Mining
> [This posting is provided "AS IS" with no warranties, and confers no
> rights.]
>
> "See Lim" <seel@symmetrics.net> wrote in message
> news:u$6Yfa67FHA.1188@TK2MSFTNGP12.phx.gbl...
>
>

Renato

2005-11-23, 7:24 am

I get no problem creating the linked server but when I use openquery I
always get:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSOLAP" for linked server "AdventureWorksDW"
reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "MSOLAP" for
linked server "AdventureWorksDW".

This happens no matter what query and also occurs for other OLAP
databases.

Has anyone had any success at all with linked servers?

Akshai Mirchandani [MS]

2005-11-23, 8:24 pm

Probably SQL Server is running as a user that doesn't have permission to
connect to the AS server. Run the Profiler against the AS 2005 server and
see which user is failing to connect...

HTH,
Akshai

--
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Renato" <renato_buda@iprimus.com.au> wrote in message
news:1132735880.137398.206150@g49g2000cwa.googlegroups.com...
>I get no problem creating the linked server but when I use openquery I
> always get:
>
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "MSOLAP" for linked server "AdventureWorksDW"
> reported an error. Access denied.
> Msg 7350, Level 16, State 2, Line 2
> Cannot get the column information from OLE DB provider "MSOLAP" for
> linked server "AdventureWorksDW".
>
> This happens no matter what query and also occurs for other OLAP
> databases.
>
> Has anyone had any success at all with linked servers?
>

Sponsored Links