Setting up linked server to OLAP in SQL2005

[amomar]

I'm having problems connecting to Analysis Services 2005 via a linked server. I'm selecting:

Microsoft OLE DB Provider For Analysis Services 9.0

as the provider and also entering values for the data source and catalog. It wouldn't allow me to enter just these details and requested a product. I've entered MSOLAP.

When I try to connect to this linked server I get the message:

"Cannot connect to the server servername. The server is either not started or too busy."

I'd had this error before when using SQL2000 but SP3 fixed it. Any suggestions on how to get around this again would be appreciated.

 

[mrdenny]

Can you post a sample query to query a cube via the linked server? I've never queried a cube like this before.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

I can't seam to get the FROM syntax correct to access a cube, but I'm getting a can't find the table error.

When I setup the linked server I Did the following.
Linked Server: MyASServer
Provider: Microsoft OLE DB PRovider for Analysis Services 9.0
Product Name: TestServer
Data Source: TestServer

I left everything else blank.

On my 2005 server AS and SQL are installed on the same server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[amomar]

The way I'd access the cube is by sending MDX through Openquery from a proc:

SET @nv_mdx =
'WITH
SET [Days] AS ''''{[years].[time]}''''
SELECT {Days} ON 0,
[Product].[Product].members
DIMENSION PROPERTIES
MEMBER_CAPTION,
[Product].[Product Division ID]
ON 1
FROM
Sale'
SET @nv_sql = 'SELECT * FROM OPENQUERY(olap,' + '''' + @nv_mdx + '''' + ')'
EXEC sp_executesql @nv_sql

 

[mrdenny]

I'm getting the following error back

OLE DB provider "MSOLAP" for linked server "TestServer" returned message "The Test cube either does not exist or has not been processed.".

It's probably just because I'm not sure how to format the query against my cube. However I'm deffinetly getting back to the AS server and getting a response.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[amomar]

I seem to remember getting this as an erroneous error in the past on 2K. How can you tell that you're getting a response from AS?

 

[mrdenny]

With my limited knowledge of AS I'm working off the fact that it's not a connection message so I'm assuming that it is connecting.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com