I am trying to run the following query:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Somefile.xlsx',
'SELECT * FROM [Sheet$]')
But I get this error:
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
I tried the following:
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
And:
USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
When I go to 'Server Objects' -> 'Linked Servers' -> 'Providers', Microsoft.ACE.OLEDB.12.0 is listed.
Acess Database Engine x64 is installed, all Office products are 64-bit, and my SQL Server is also 64 bit.
If it matters, all users have access to the Temp folder in the Users folder.
(These are all suggestions I found in similar answers to this question)
Edit: Using SQL Server 2014.
I found from this blog the two missing steps needed to get it working for me.
You can also get it working without needing
AllowInProcess
if you follow the instructions in this MSDN article. The core instructions being:Make sure you close the excel spreadsheet and run SSMS as admin.
I also met this issue and did the steps as yours,finally I met the error as yours. In the end, I use a SuperUser account and use this script as below and the issue has been resolved.