Creating a linked server in SQL Server 2012 for analysis services with the connection string consisting of the instance name

Question

I was creating a linked server from SQL database to Analysis services using the server ip along with the instance name. While doing so I got the following error:

Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "TEST". OLE DB provider "MSOLAP" for linked server "TEST" returned message "Errors in the OLE DB provider. Could not connect to the redirector. Ensure that the SQLBrowser service is running on the '*****' server.". OLE DB provider "MSOLAP" for linked server "TEST" returned message "Errors in the OLE DB provider. An error occurred while named instance information was being retrieved from the SQLBrowser service on the '*****' server.".

So I created an alias for the server along with the instance name. After doing so got the error below:

Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "TEST". OLE DB provider "MSOLAP" for linked server "TEST" returned message "The following system error occurred: The requested name is valid, but no data of the requested type was found. ".

Answer 1

The above issue existed due to an issue in the port no for the analysis services. Kindly refer to the link below : http://msdn.microsoft.com/en-us/library/ms174937.aspx

Following the steps mentioned in the above link I observed that the analysis services for the named instance was using a different port number. with "MSOLAP$InstanceName service" determined which port was being used. Using the linked server script created the linked server.

EXEC sp_addlinkedserver @server = 'server', @srvproduct = 'SSAS', @provider = 'MSOLAP', @datasrc = 'IPAddress:portnumber', @catalog = 'catalog'