Thursday, March 22, 2012

Can not connect to AS 2005 from Excel

I'm having issues connecting to AS 2005 sp1 from Excel 2003. I have tried
two configurations as follows:

Secario 1
Installed on a Windows Server 2003 is AS 2005 sp1.
Installed on a workstation with XP sp2 is Office 2003, msxml parser 6.0, SQL
Server AS ADOMD, Excel Add-in for SQL Server Analysis Services sp1.

Secario 2
Installed all on workstation with XP sp2: Full install of SQL Server 2005
Dev Edition, Office 2003, msxml parser 6.0, SQL Server AS ADOMD, Excel
Add-in for SQL Server Analysis Services sp1.

In both cases the error returned is server does not exist or is too busy.

The cube is created and can be browsed from BI Studio.
The LAN id has been added to the AS 2005 security.
All SQL Services are started including Browser and AS.
Firewalls are not enabled.
Local and remote clients are allowed to connect to AS.
I can open TCP port 2383 with telnet on the AS server from the workstation.

Any one see anything I've missed or can shed some light on why the server is
not allowing connections?

Thanks,
Danny

Make sure you install "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider " on you client machine

You can get it from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I think this gets installed with SQL 2005 locally but I reinstalled it. Unfortunately this had no impact and the error message is the same.|||

Take a look at this post: http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

In addition: Another way to test connectivity:

1. Create new empty text file.
2. Change it's extension to .udl
3. Double click on the file, and in the "Data Link Properties" dialog go to the "Provider" tab. Select there "Microsoft OLE DB Provider for Analysis Services 9.0" then "Next".
4 On the connection page type name of your server and select "Use Windows NT integrated security"

At this point you should be able to list of databases on Analysis Server.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Edward. It succeeded on the Test connection and the initial catalog select list populated with the SSAS database names. Looks like the server is responding and the OLE DB driver for AS 9.0 is working. That narrows the issue down to MSExcel 2003 and/or the Addin.|||Issue somehow resolved itself after perfroming a repair on Excel following by reregistering the OLEDB 9.0 dll.sql

No comments:

Post a Comment