Search code examples
sql-serverexceloledbopenrowset

OPENROWSET with Excel file returns DBSCHEMA_TABLES_INFO error

I’m using SQL Server 2017 Developer Edition (RTM-CU30) and Office Professional Plus 2016, both 64-bit, on Windows 10 Pro.

I created a simple Excel workbook with the following entries and saved it to C:\Book1.xlsx:

ID  Name
1   Apple
2   Banana
3   Cherry

I then used SSMS 18 to run the following query:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=C:\Book1.xlsx', [Sheet1$])

If the AllowInProcess option is disabled for Microsoft.ACE.OLEDB.12.0, then the following errors occur:

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 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.

(Process Monitor shows that dllhost.exe is able to read the file, so this doesn’t appear to be a configuration or permission issue.)

If the AllowInProcess option is enabled for Microsoft.ACE.OLEDB.12.0, then SQL Server crashes (sqlservr.exe terminates unexpectedly) and the following error occurs:

Msg 109, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)

I tried using Microsoft.ACE.OLEDB.16.0 instead of Microsoft.ACE.OLEDB.12.0, but that produced the same errors.

However, the 64-bit Import and Export Data wizard worked fine.

Why doesn’t OPENROWSET work properly?

Solution

  • To resolve the errors, download and install the Microsoft Access Database Engine 2016 Redistributable. This downgrades ACECORE.DLL (the "Microsoft Access database engine DLL") from version 16.0.15427.20060 (which comes with Office Professional Plus 2016 and is evidently buggy) to 16.0.4999.1000 (which works fine with OPENROWSET).

    Note: Both versions of ACECORE.DLL will remain on your system. The redistributable setup program updates the Registry to point to the downgraded version.

    • Original location: C:\Program Files\Microsoft Office\root\vfs\ProgramFilesCommonX64\Microsoft Shared\OFFICE16
    • Updated location: C:\Program Files\Common Files\microsoft shared\OFFICE16

    Caution: Repairing or re-installing Office may cause the buggy version of ACECORE.DLL to be registered again.