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:
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.
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.