RESOLVED! ActiveX Component Can't Create Object: Cube build failure against SQL Server 2005 Analysis Services
The original posting is still repeated below to help with other's troubleshooting. I uninstalled the client tools to double check if the original instructions were OK. It turned out that I didn't have the SQL Server 2005 Management Objects installed - even though when I tried loading it again it told me I hade a "higher version". (It also didn't show in Add/Remove in control panel - my clue to home in on this component).
To find out more about this "higher version" message I re-ran the msi with the /l* <logfilename> parameters to get a more complete log file. This identified a component that was triggering the "higher version" message. So I searched the registry for this GUID and identified it as the SQL Server Reporting Services 2005 Add-in for Microsoft SharePoint Technologies - which is a neat add-in to get SSRS report libraries in SharePoint and is a SQL Server 2005 SP2 feature. I uninstalled this add-in and could then load the Management Objects. I re-applied SP2 and then I could build cubes!
The specific thing that identifies this issue is that in Process Monitor I found that the system could not find the key HKCR\CLSID\{554BBCA3-925F-4797-9460-2421A8CD7030} - I guess that the client tools has the bits I needed - as well as the Management Objects.
I then re-loaded the SharePoint add-in and it said a lower version was present; did I want to upgrade - so somehow these two components are tied together.
UPDATE - re-installing the add-in removed the key again and cube building breaks! Remove it and add back the Management Objects and all is OK. I'll search for a workaround that enables both to coexist...
ORIGINAL POSTING BEGINS HERE...
I've been working on my own servers and testing cube builds to remote servers this week, and one of the combinations of servers gave me this error - ActiveX Component Can't Create Object. I quickly fell in to the "one error - one solution" trap and assumed I hadn't installed the components required - so re-visited the KB and checked. They were all apparently there. So I compared to a running system and ran Process Monitor to try and catch the differences. It seemed the first failure point was trying to find Microsoft.AnalysisServices.DLL and the working server had this as it also had the full install of Analysis Services 2005 - even though for my tests I was building a cube on a remote server.
The long and the short is that I then loaded the client tools for SQL Server 2005 on my application server and it all started working. Not sure at this stage if it came from the management tools, connectivity tools, SDK or the legacy components - but I will have a look around next week and see what more detail I can find. I'm not keen on giving workarounds like loading a set of stuff you don't really need - but if it gets someone out of a hole...
One of the purposes of my work is to get a new KB together that serves for Project Server 2007 the way that KB 921116 serves for 2003. We usually refer people to this KB even for 2007 - but the hotfix part of the KB is irrelevant for 2007 - so it can get confusing. I'd like to get to the bottom of this particular issue and cover as many failure modes as possible.
If you haven't used Process Monitor then it is a cool tool. A combination of filemon and regmon that shows all file and registry access and you can track what accounts are doing what.
Technorati Tags: Project Server 2007