tsql - Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Question

When I run the following commmands in SQL Server 2012

 exec sp_configure 'Advanced', 1 RECONFIGURE
 exec sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE
 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
 EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

 SELECT * INTO dbo.Normalization FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\_sandboxConsolidations.xlsx','SELECT * FROM [Codes and Values$]')

I get this error

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Any idea why?

Answer 1

Is the provider installed? The Microsoft.ACE.OLEDB.12.0 provider isn't there by default and so if you haven't installed it the it won't be able to be initialised.

Have a look in SQL management studio in Server Objects -> Linked Servers -> Providers and see if it is listed.

If not, you need to install the Microsoft Access Database Engine (either x86 or x64 depending on your SQL server install) which will install the provider for you.

Download the software here:

Microsoft Access Database Engine 2010 Redistributable