OLE DB provider 'MSOLAP' reported an error

Question

Post reply

OLE DB provider 'MSOLAP' reported an error

jlbernardo

SSC Rookie

Points: 44
More actions
November 8, 2010 at 11:12 pm

#147551

Hi,

Has someone got an idea where I went wrong while using the OPENROWSET (MS SQL Server 2000) function to query the Analysis Services (MS SQL AS 2000) database ?

My configuration is I have SQL Server setup on Server_A and Analysis Services is setup on Server_B. Both servers are using SQL Server Login on the same domain.

I am running the default instance of SQL Server_A on Server_B using Query Analyzer to query the Analysis Services OLAP database which exists on Server_B. I used SQL Server Login to execute the below script:

SELECT *

FROM OPENROWSET ('MSOLAP', 'DATASOURCE=[Server_B];Initial Catalog=[My_OLAP_DB];', '

SELECT

{ [Measures].[Total Population] } ON COLUMNS,

Filter ( [Geography].[TXR].Levels(1).Members, NOT IsEmpty( [Measures].[Total Population] ) ) ON ROWS

FROM [My_OLAP_DB]')

I am getting the below error:

Server: Msg 7399, Level 16, State 1, Line 2

OLE DB provider 'MSOLAP' reported an error.

[OLE/DB provider returned message: Database 'My_OLAP_DB' does not exist.]

OLE DB error trace [OLE/DB Provider 'MSOLAP' IDBInitialize::Initialize returned 0x80004005: ].

Obviously, the error states the OLAP database does not exist eventhough it exists. Using "sa" login to execute the above script I do not encounter any error.

For further details I am using SQL Server with 8.00.769 (SP3) under Windows 2003 (SP2) 32 bit Standard Edition.

I would greatly appreciate any feedback.

Thanks & best regards,

tree_node

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Obviously, the error states the OLAP database does not exist eventhough it exists. Using "sa" login to execute the above script I do not encounter any error.

check the account permissions...?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

jlbernardo SSC Rookie Points: 44 More actions · Answer 2

jlbernardo

SSC Rookie

Points: 44

November 8, 2010 at 11:22 pm

#1248019

Hi,

Thanks for your feedback. OLAP Services is using SQL Server Login same with Analysis Services and SQL Server. These two servers are also on the same domain.

Regards,

tree_node

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

Fair enough, but you only mention

OLAP Services is using SQL Server Login same with Analysis Services and SQL Server. These two servers are also on the same domain.

...

confirm if this SQL Account you use, has sufficient access to the destination.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

jlbernardo SSC Rookie Points: 44 More actions · Answer 4

jlbernardo

SSC Rookie

Points: 44

November 8, 2010 at 11:39 pm

#1248025

Hi Henrico,

Yes, SQL Server Account is a member of OLAP administrator. I made sure too this account has access to everything. I not sure if the issue I am encountering has something to do with Windows Authentication, I have already run out of idea.

Regards,

tree_node

jlbernardo SSC Rookie Points: 44 More actions · Answer 5

jlbernardo

SSC Rookie

Points: 44

November 10, 2010 at 1:20 am

#1248698

Hi Everybody,

Are there any ideas on how to correct the issue?

I appreciate very much any feedback.

Thanks,

tree_node

Ian Massi SSCertifiable Points: 5931 More actions · Answer 6

Ian Massi

SSCertifiable

Points: 5931

November 11, 2010 at 6:15 am

#1249444

Have you added the Analysis Services server as a linked server on Server_A?

If that suggestion doesn't help perhaps checking the security logs on Server_B might give you some insight on what's going on.

jlbernardo SSC Rookie Points: 44 More actions · Answer 7

Hi Ian,

Thanks for having look in to this issue.

I am using the registered version of OLAP Services which is MSOLAP, and I am using this as a provider using OPENROWSET function. As you can see the query on the top of this thread, the query works fine under Windows 2000 Server on two servers on the same domain. However, I am encountering problem when executing the same query under Windows 2003 Servers.

Investigating further regarding the problem, I found out this error while manually adding SQL Server Login account under "Manage Role" of Analysis Services: "The trust relationship between the primary domain and the trusted domain failed."

I am not sure if this error has something to do with the one on the top while executing the query for both servers are on the same domain.

Looking forward for anything you can share.

Regards,

tree_node