I had similar problem and I made it work for me.
To resolve the issue please follow bellow instructions very carefully
- Download the Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit version from following link: https://www.microsoft.com/en-us/download/details.aspx?id=13255
- Install it on your server.
- Please check the user running SQL Server and make sure that user has access to the temp directory C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp if it is a local service account or C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp if it is a network service account.
- Configure 'Ad Hoc Distributed Queries' and enable the Microsoft.ACE.OLEDB files as mentioned bellow:
Bellow are the SP_CONFIGURE commands:
SP_CONFIGURE 'show advanced options', 1;
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1;
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1
On newer SQL Server 2014 You need to use 'DynamicParameters' instead of 'DynamicParam'
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
Please make sure that you register msexcl40.dll as mentioned bellow: