Unable to Create Linked Server for SSAS Provider MSOLAP

Recently I ran into an odd scenario. Net new SQL servers were built in Dev, QA and Prod. I had to create Linked Servers to SSAS. All of these were on SQL 2012 enterprise Sp2 + Kb2969896. All was great till I was unable to create linked server for SSAS provider MSOLAP.

The 32-bit OLE DB provider “MSOLAP” cannot be loaded in-process on a 64-bit SQL Server. (SQL Server, Error: 7438)MSOLAP Error

   

I had forgotten the fact that SQL server works in mysterious ways when it wants to!

The error was self explanatory. SQL was trying to use 32-bit drivers instead of 64-bit. Please note that this is a 3 phase job:

  1. Unregister DLLs
  2. Register DLLs in the correct order
  3. Restart SQL Services

Locate the file location for msolap110.dll and then run the following on the command prompt:

Unregister DLLs

regsvr32 /u "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

regsvr32 /u "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

Register DLLs in the correct order

regsvr32 "C:\Program Files (x86)\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

regsvr32 "C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msolap110.dll"

Once the services were up, I tested the linked server and it was successful. If you are doing in on a cluster ensure you start with the passive node(s).

~ Adios

Khan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |

Follow me on Twitter

   

About Arsalan Khan

People know me as 'Khan' ... nope not Shah Rukh's movie but Star Trek II - Wrath of Khan and since than 'Khan' it has been. Born and raised in Dubai before moving to US for my masters. Been blessed with solid 8+ yrs with SQL (and counting) and currently working for the biggest publishing house as Sr. Database Administrator. I have had an opportunity to speak about tips & tricks to write efficient tsql for Quest International Users Group (PeopleSoft). Love playing TT, badminton and thoroughly enjoy watching cricket when not occupied with my loving daughter. Expertise in DR, Performance Tuning, Troubleshooting and Problem Solving. With that being said I have finally decided to roll my sleeves up and give back to the community bit by bit. Finally.. as my wonderful wife puts it ... "if you don't have a smile, I will give you one of mine ~ Rabia Khan" ~Cheers

View all posts by Arsalan Khan →

One Comment on “Unable to Create Linked Server for SSAS Provider MSOLAP”

  1. Hi Arsalan,
    It seems it does not work only this solution.
    Windows 7 x64, Excel 2013 (on Excel 2007 it works)

    Data conection definition is:
    Provider=MSOLAP.2;Integrated Security=SSPI;Persist Security Info=True;Data Source=192.168.0.200;Initial Catalog=Serstill;Client Cache Size=25;Auto Synch Period=10000;MDX Compatibility=1

    I have unregistered both msolap110.dll, restarted the PC, register both msolap110.dll and restarded the PC again => “Initialization of the data source failed”

Leave a Reply

Your email address will not be published.