I just configured a new SQL server to utilize OPENROWSET to query Excel spreadsheets. I am using Windows Server 2016 Standard, Microsoft SQL Server 2017 14.0.3045.24 (X64), and have installed Microsoft Access Database Engine 2016 Redistributable (x64).
I am able to successfully query some Excel files, but not others which I have previously queried successfully on other servers. Here is a sample query and the error returned:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
'Excel 12.0 Xml;
Database=D:\load\test.xlsx',
[load$])
This is the error returned:
Msg 7311, Level 16, State 2, Line 26
Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.
I also tried switching to Microsoft.ACE.OLEDB.12.0, but get the same error. It seems to be caused by certain data, as I can cut and paste data from a non-working spreadsheet to a working spreadsheet and make the error occur. I have not been able
to figure out what data (or quantity of data?) triggers the problem. But I am assuming there is something not quite right about my configuration. I am having great difficulty finding any good information on this issue. Hoping someone here
can help.
Thanks, Dan
