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.
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:
- Unregister DLLs
- Register DLLs in the correct order
- Restart SQL Services
Locate the file location for msolap110.dll and then run the following on the command prompt:
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).