1

I'm interested in gathering some DMV data from our SSAS cubes and store it on our management box. When I create the linked server for this effort I run into an error establishing the connection.

TITLE: Microsoft SQL Server Management Studio
------------------------------

The linked server has been created but failed a connection test. Do you want 
to keep the linked server?

------------------------------
...

------------------------------

Cannot initialize the data source object of OLE DB provider "MSOLAP" for 
linked server "PROD\SSAS08R2".
OLE DB provider "MSOLAP" for linked server "PROD\SSAS08R2" returned 
message "XML for Analysis parser: The MSOLAP property was not recognized.".
OLE DB provider "MSOLAP" for linked server "PROD\SSAS08R2" returned 
message "Errors in the OLE DB provider. An error occurred while named 
instance information was being retrieved from the SQLBrowser service on the 
'PROD' server.". (Microsoft SQL Server, Error: 7303)

I'm using these settings when creating the linked server.

EXEC master.dbo.sp_addlinkedserver @server = N'PROD\SSAS08R2'
, @srvproduct=N'MSOLAP'
, @provider=N'MSOLAP'
, @datasrc=N'PROD\SSAS08R2'
, @provstr=N'MSOLAP'
, @catalog=N'Prd_Analysis_Services'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PROD\SSAS08R2'
, @locallogin = NULL 
, @useself = N'False'
, @rmtuser = N'WINAD\SrvcAcct'
, @rmtpassword = N'XXXXXX'

What do I need in order to get around this error and make a successful connection to SSAS? Is there a better/easier way to extract DMV data from SSAS?

  • As per sqlblog.com/blogs/stacia_misner/archive/2010/11/30/31193.aspx Provider: Microsoft OLE DB Provider for Analysis Services 10.0 Product name: MSOLAP.4 Data source: (the name of your server) Catalog: (the name of the Analysis Services database) – SqlWorldWide Apr 4 '17 at 18:03
  • In my list of providers, MSOLAP is listed but MSOLAP.4 is not. When I select the properties for MSOLAP in the provider list in SSMS, I do see it as the 10.0 provider for SSAS. Can I not use this provider? – Geoff Dawdy Apr 4 '17 at 19:22
  • I doubt if that is the issue. Is your browser service running? – SqlWorldWide Apr 4 '17 at 22:57
  • @SqlWorldWide Just verified... the browser service is running. – Geoff Dawdy Apr 5 '17 at 15:09
  • Few things in case if you did not check. Account has permission. Reinstall the provider, hard code linked server with port number – SqlWorldWide Apr 5 '17 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.