Problem with Analysis Services Provider

Hi,

For some reason I cannot execute reports using the provider "Microsoft SQL Server Analysis Services (AdomdClient)". I can see my cube through the Query Builder and execute the queries with it too, but when I run the query I get an error...

An error has occurred during report processing.
Cannot create a connection to data source 'SalesMartCube'.
A connection cannot be made. Ensure that the server is running.
No connection could be made because the target machine actively refused it

When I change the provider to "Microsoft OLE DB Provider for Analysis Services 9.0" I can execute the report, but I lose the ability to use the MDX query builder with this provider!

Can someone tell me how I can get the analysis services provider to work or how I can get the mdx query builder to work using the old db propvider?

I have no trouble connecting to the Analysis Services server using the sql 2000 "mdx sample application" and I can also connect to it with excel by importing a datasource. But I cannot connect to it from sql management studio 2005, I get a similar error to above. Could it be that sql server 2005 cannot connect to AS2000?

Thanks,

Lachlan

Did you try to add the ConnectTo property to your connection string when connecting to AS 2000:

data source=localhost;initial catalog=SalesMartCube; ConnectTo=8.0

-- Robert

|||

Hi Robert,

I tried your suggestion but I get a different error when I click on the preview pane using "ConnectTo=8.0" in the connection string...

An error has occurred during local report processing.
An error has occurred during report processing.
Cannot create a connection to data source 'SalesMartCube'.
The LCID or Compare String flags have already been set by an existing connection. They cannot be changed.

It seems strange to me that I can see the query results in query builder but get an error on the preview pane.

I've also noticed that some of the query builder features are not available to me such as query parameters, command type and query designer. Is this because I am connecting to AS2000 rather than AS2005?

Thanks for your help.

Lachlan