Unabel to connect to SQL 2005 Analysis Services in SQL Management Studio

Unabel to connect to SQL 2005 Analysis Services in SQL Management Studio

• Primillo

  Did you use a named instance when installing Analysis Services If yes, you will need to specify it in the connection dialog as Server name: localhost\instance_name. Otherwise you will see the error you just described.

  Another problem might be that the AS service is not running. This you can verify in Start / Settings / Control Panel / Administrative Tools / Services.

  
  
  

• BobTheBuild

  Did you get the answer of this error then please publish this one to this blog i am facing the same problem.
  
  

• Bruce Williams

  Mary,

  What's the name of the AS 2005 service Does it run under another service I think I'm having the same problem, but I can't find the service.

  
  

• Lalaa

  Many thanks for the input.

  Let me provide more detailed information, so that people might be able to

  better help on this case.

  The name of my computer is MyComputer. There are two registered servers in

  SQL 2005 Management Studio(installed in my desktop computer): They are [1]MyComputer . [2]MyComputer\sqlexpress.

  I open SQL Server Business Intelligence Studio to do Analysis Services.

  After opeing a project (test1) by Cliking "File-New-Project-Business Intelligence Projects-Analysis Services Project", I right-click the Data Source

  tab in the solution explorer, and in the Server name, I enter localhost, and for the database name,

  I enter AdventureWorksDW, then I do other analysis(creating Cube and Dimensions),

  everything is fine. When analysis is done, I right-click the project name(test1), and select Properties tab, and on the left Configuration Properties pane, I click deployment tab, then on the right pane on the Target Server tab, I type localhost(actually this is the default name). When back on the solution explorer, I right-click the project name (test1), and then select Deploy tab, I get the following message:

  "The project could not be deployed to the "localhost" server because one of the following connectivity problems: A connection cannot be made. Ensure that the server is running. To verify or update the name of the target sever, right-click on the project

  in the solution explorer, select project Properties, click on the deployment tab,

  and enter the name of the server". I also enter various combinations of server name,

  none of them can get it right.

  Then, I open the second test project (called test2). The only difference

  between the previous project and this project is that in this project,

  in the Server name, I enter " MyComputer" becasue this is one ot the two

  registered server names on SQL Management Studio, and in the name of

  database, I enter "AdventureWorksDW" as before, then I do other

  analysis(like creating Cube and Dimensions), everything is fine. At

  time of deployment, after clicking the properties tab of this project (test2),

  I click the deployment on the right pane, then on the target server entry in the

  right pane, I try various combinations of server name, like "MyComputer\MyComputer" and "MyComputer", others, none of them works. The error is about the same as in the previous project(test1).

  Afte these two failures, I open the SQL management studio to see

  if there is anything wrong with Analysis services.

  For the server type, I enter "Database Engine", for the server name, I

  enter "MyComputer", then I can enter the management studio. Then I

  quit the management studio, and want to reopen it . This time,

  for the server type, I enter "Analysis Services", for the server name,

  I enter "MyComputer", after clicking on the connect tab, I receive the following error message:

  "Cannot connect to MyComputer.

  Additional Information:

  A connection cannot be made, Ensure that the server is running

  (Microsoft Analysis Services.AdomdClient). No connection could be made

  because the target machine actively refused it (System)."

  On the other hand, I have no problem in entering and using

  "Integration Services", and "Reporting Services" in Management studio.

  I can also program ADO.NET for Windows Form and ASP.NET in Visual Studio 2005.

  I also check the administrative tools to see that the

  SQL Server Analysis Services is on by cliking "start-all programs-administatvie tools-services".

  Is there anything wrong here I hope that the above detailed information

  will be useful for someone who is not in the front of my computer to know what is

  going. Any help is much appreciated.

  
  
  

• Michael Koltachev

  The name of the service is: SQL Server Analysis Services (Instance_name)

  "Instance_name" appears only if the server was installed as named instance.

  
  
  

• hatem elshenawy

  It seems you are missing the engine of SQL Server Analysis Services although you have installed client tools for Business Intelligence (since you can run SQL Server Business Intelligence Studio).

  Run the setup again and try adding the Analysis Services server. It is in one of the options in the setup wizard.

  
  
  

• Dave Rollins

  It's not there. Do I need to re-install Or, can the service be added manually

  Current SQL services are below:

  SQL Server (MSSQLSERVER)

  SQL Server Active Diretory Helper

  SQL Server Agent (MSSQLSERVER)

  SQL Server Browser

  SQL Server FullText Search (MSSQLSERVER)

  SQL Server Integration Services

  SQL Server Reporting Services (MSSQLSERVER)

  SQL Server VSS Writer

  The AS 2000 service is still there, but is stopped (MSSQLServerOLAPService).

  
  

• wiertmir

  Will do. Our Data Center Engineering staff did the install, so I didn't have a chance to review all the options. :( Thanks.

  I didn't mean to hijack the thread, but I thought this info might help the original poster.

  
  

• beelzeboris

  hello,

  it is not quite clear why the connection fails.

  to clarify on the server name format: if the connection is to default instance - you'd just provide the name of the box (or can be localhost). if to the named instance - then <machine name>\<instance name>. when the named instance is used, SQL Browser service should be running as well.

  can you try to re-start AS service - and see if can connect after that

  can you try to connect with profiler (All Programs->Microsoft SQL Server->Peformance Tools->Sql Server Profiler; New Trace, server type=Analysis Services; ) does it succeed

  to double check: is this a local connection (sometimes remote ones can fail if firewall is running and AS ports are not enabled)

  thank you,

  
  
  
• Unabel to connect to SQL 2005 Analysis Services in SQL Management Studio