Is this really working ?
here is the error message I am getting, I have this 'FoodMart 2000' DB on
OLAP Server
OLE DB provider 'MSOLAP' reported an error.
[OLE/DB provider returned message: Database 'FoodMart 2000' does not exist.]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IDBInitialize::Initialize
returned 0x80004005: ].

EXEC sp_addlinkedserver
@server='olap_server',
@srvproduct='',
@provider='MSOLAP',
@datasrc='OLAPServer',
@catalog='foodmart 2000'

go

-- MDX in OPENQUERY --

SELECT * FROM OPENQUERY(olap_server,
'SELECT
{ Time.Year.[1997] } ON COLUMNS,
NON EMPTY Store.MEMBERS ON ROWS
FROM Sales
WHERE ( Product.[Product Category].[Dairy])' )

I know this works from the AS Operations
Guidehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspxIf
you are still having problems then it might be a two-hop NTLM credentials
problem.Remember that with NTLM you only get one hop (from client to server)
you cannotdelegate further (i.e. to a 3rd AS server).USE master
GO
/* Additional examples are available at: */
/* http://support.microsoft.com/default...kb;enus;218592 */
/* --------------------------------------------------------------*/
/* Remove any previous references to the linked server */
EXEC sp_dropserver 'LINKED_OLAP'
EXEC sp_addlinkedserver
@server='LINKED_OLAP', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.2', -- OLE DB provider
(the .2 means the SQL2K version)
@datasrc='localhost', -- analysis server name (machine name)
@catalog='Foodmart 2000' -- default catalog/database
select * from openquery
( LINKED_OLAP, 'Select {[Measures].[Unit Sales]}
on columns from [Sales] ')
select * from openquery
( LINKED_OLAP, ' with member [Measures].[Store Profit Rate]
as ''([Measures].[Store Sales]-[Measures].[Store
Cost])/[Measures].[Store Cost]'', format = ''#.00%''
select {[Measures].[Store Cost],[Measures].[Store
Sales],[Measures].[Store Profit Rate]} on columns,
Order([Product].[Product Department].members,
[Measures].[Store Profit Rate], BDESC) on rows
from Sales where ([Time].[1997]) ')
select * from sysobjects order by name

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Kris" <Kris (AT) discussions (DOT) microsoft.com> wrote

Besides the multi-hop problem, another possibiilty would be if Foodmart 2000
did not have an Everyone role; or if there is a permissions problem (i.e. we
hide databases if you don't have access to them).

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Kris" <Kris (AT) discussions (DOT) microsoft.com> wrote

Dave Thanks for your reply.

I am admin on server, so permission is not a problem. I am still having
problem.
We are heavy OLAP users and I need to get result from OLAP Cube and store
into DB, I appercaite your help.. Both link gives me error like page not
fould ..

OLE DB provider 'MSOLAP.2' reported an error.
[OLE/DB provider returned message: Database 'Foodmart 2000' does not exist.]
OLE DB error trace [OLE/DB Provider 'MSOLAP.2' IDBInitialize::Initialize
returned 0x80004005: ].

Thanks
-Kris



"Dave Wickert [MSFT]" wrote:

0x80004005 is "Access is denied".
I think this is more of a core OLEDB issue. Are you able to create linked
servers with other OLEDB data source? Is everything on the same machine?
User issuing the query, SQL RDBMS service and the AS service ? Or different
machines?

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Kris" <Kris (AT) discussions (DOT) microsoft.com> wrote

I have linked server for SQl and I am able to run query. My AS server is on
different server. still no luck same error .... Thx.

"Dave Wickert [MSFT]" wrote:

Ok. So AS is on one computer and the SQL RDBMS is on a different computer.
Two questions:
1) Where are you running the query? On the SQL RDMS itself or from a
separate workstation? Please try it from the SQL RDMS server itself.
2) Also, is the service account for SQL Server a domain account which has
permissions to access FoodMart?
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Kris" <Kris (AT) discussions (DOT) microsoft.com> wrote

Dave, Thanks for your help.

1) I am running from SQL Server itselef.
2) Yes. Domain account with admin rights.. and full access to FoodMart..

3) I found other way. I checked my result into MDX qry analyzer and after
that I created DTS using OLEDB provider for OLAP and down load result to SQL
....works perfect...Thank you very much for your help...But it is still puzzle
for me why i couldn't able to setup..

"Dave Wickert [MSFT]" wrote:

OLAP Linked server don't work after service pack 4 for SQL Server
installation!!!
If You use linked OLAP server - don't install SP4!!!

"Kris" wrote: