I have a SQL Server 2012 Standard edition sp1 that has ssas also installed.
From this server I create a linked server to a ssas server called BIREPLON1, but when I try to access it, I get the error message shown below.
the funny thing is that when I go through GUI, by clicking Server Objects\linked servers\BIREPLON1 all seem to be ok (as you can see below on picture number 4).
But when I run the simple
SELECT CATALOG_NAME FROM OPENQUERY([BIREPLON1], 'SET FMTONLY OFF;SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') as Radhe
I get this error message:
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "BIREPLON1".
Have I missed out on something?
I found this article here.
There it states that:
To resolve that problem you have 3 options:
Create the linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'SSASSERVER', -- name of linked server @srvproduct=N'MSOLAP', @provider=N'MSOLAP', -- see list of providers available on SQL Server under Linked Server node in SSMS Object Browser @datasrc=N'ServerName', -- machine or instance name that host Analysis Services @catalog=N'SimplifiedCube' -- Analysis Services database (cube)
and adding a linked server login:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SSASSERVER', @useself=N'False', @locallogin=NULL, @rmtuser=N'myDomain\Login', @rmtpassword='########'
I have basically just done the number 3 and the linked server is working fine for me now. As you can see on the picture below.
Also I had problems with the timing out so I had to change this setting as well: (the default is 600)
EXEC sys.sp_configure N'remote query timeout (s)', N'6000' GO RECONFIGURE WITH OVERRIDE GO
This can also be done through the GUI as per the picture below: