Invoke-Sqlcmd : The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server
Problem description : The PowerShell command below uses to run every 5 minutes. But since 2 days, I got the error message below : Invoke-Sqlcmd : The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server.
(Invoke-Sqlcmd -ServerInstance $R -Database SSAS_Monitor -Query "INSERT INTO dbo.SSAS_DISCOVER_SESSIONS SELECT '$I', getdate(), * FROM OpenRowset('MSOLAP', 'Data Source=$I;' ,'SELECT * FROM `$system`.DISCOVER_sessions WHERE SESSION_USER_NAME <> ''$proxy''')")
Work done :
- I had a look to windows event system and application but I didn’t see any error about that.
- I didn’t see any reboot or new installation of any Hotfix or whatever.
- I can see that provider for 32bit and 64bit are installed for MOLAP.5 (2012 RTM for the both)
- I can see that provider for 32Bit only was installed for MOLAP.4 (2008 R2 RTM)
- Process Monitor didn’t give me any clue.
- I restarted the server just in case, but theissue was still there.
Workaround : It looks like my query is using now MOLAP.4 instead of MOLAP.5 (I guess but not sure). As the version for 2008R2 is installed only in
32bit,the script failed with the message The 32-bit OLE DB provider "MSOLAP" cannot be loaded in-process on a 64-bit SQL Server
Step 1 : Unregister the both DLL:
regsvr32 /u "C:Program Files (x86)Microsoft Analysis ServicesAS OLEDB110msolap110.dll"
regsvr32 /u "C:Program FilesMicrosoft Analysis ServicesAS OLEDB110msolap110.dll"
Step 2 : Register the both DLL, with 32Bit in first position:
regsvr32 "C:Program Files (x86)Microsoft Analysis ServicesAS OLEDB110msolap110.dll"
regsvr32 "C:Program FilesMicrosoft Analysis ServicesAS OLEDB110msolap110.dll"
Step 3 : Restart SQL Service
After that, everything was working well :-)
Relative links :
- Explanation of Regsvr32 usage and error messages
- How to perform a SQL Server distributed query with OLAP Server
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |