Errors in the OLE DB provider

[goss]

Hi all,

This is a cross-post from SQL Server Forum - No response

Using Excel 2010

Trying to connect to
MYLAPTOP\SQLDBASE\AdventureWorksDW2008R2 through Excel 2010
(Data..From Other Sources..From Analysis Services)
I entered the name of the server above which I browsed to through SSMS

But when I click next, I receive this error message:

Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the MYLAPTOP\SQLDBASE server

I checked all services in services.msc. All SQL Services are running.

What might be going on?

Kind regards.

 

[Norie]

Are you trying to connect to SQL Analysis Services?

Can you create a connection to SQL Server?

 

[goss]

Thanks Norie,

Yes, I am trying to connect to Analysis Services following this post from Will Riley on his blog.

I tried a new connection

Data..From Other Sources..From SQL Server

In the Data Connection Wizard Dialog for Server Name I entered
MYLAPTOP\SQLDBASE for the server then I was prompted for Database and Table
I chose AdventureWorkLT2008R2 and Customer and I received a worksheet with 847 records..looks good.

I then tried the same for Analysis Services

Data..From Other Sources..From Analysis Services

For the server name I tried MYLAPTOP\SQLDBASE
I received this error:

The Data Connection Wizard cannot obtain a list of databases from the specified data source

I then checked in SSMS. In object explorer I chose connect and Analysis Services. The resulting Connect To Server dialog shows the name of the server as (local). I went back to Excel and entered (local) in the Data Connection Wizard. The connection wizard came back with DMAddinsDB which appears to be related to the Data Mining AddIn from Microsoft that I installed and not the AdventureWorksDW2008R2 that I am trying to connect to.

In SSMS I see AdventureWorksDW2008R2 under Database Engine and not Analysis Services.

 

[Norie]

Goss

I've no idea if it makes a difference but in the blog Will uses localhost for the server name not (local).

I'm sure I've had problems in the past when trying to refer to a server caused by little things like that.

Perhaps worth a try.

 

[goss]

Thanks Norie,

It turns out I did not properly deploy the analysis services database

The database installer for SQL Server 2008 and SQL Server 2008R2 does not automatically deploy the Analysis Services database. To deploy the analysis services database perform the following steps:

I followed all of these instructions on Codeplex to get the Adventure Works DW 2008R2 Analysis Services Database properly deployed