I wanted to run a query from SQL Express against Excel, and I found this:

SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.14.0', 
   'Excel 8.0;DATABASE=c:docstestdata.xls', 'Select * from [Sheet1$]')

Which was just what I wanted, a way to select data from Excel. But it did not work.

Msg 7403, Level 16, State 1, Line 1

The OLE DB provider “Microsoft.Ace.OLEDB.14.0” has not been registered.

It appears that Microsoft.Ace.OLEDB.14.0 is registered as Microsoft.Ace.OLEDB.12.0. Well, I could deal with that:

SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
   'Excel 8.0;DATABASE=c:docstestdata.xls', 'Select * from [Sheet1$]')

But I still got an error:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

I looked a little further and found I needed to enable Ad Hoc distributed queries. It is an advanced option, so:

sp_configure 'show advanced option', 1
reconfigure

Then I must enable ad hoc queries:

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

The result of which can be seen with:

sp_configure

But all I got was an error when I ran my query:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider “Microsoft.Ace.OLEDB.12.0” for linked server “(null)” reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider “Microsoft.Ace.OLEDB.12.0” for linked server “(null)”.

I looked some more and I found:

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 

And now I am in business.

This was all in the past, so I hope I rewound the steps properly, to reproduce the errors.

64 Bit

Apparently there is a problem when running 32 bit Office on a 64 bit machine, you will get the following error if you try to run OPENROWSET query:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

However, I am reliably informed by Denis that 64 bit ACE on a 64 bit machine will work if you follow the steps above.