Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for Microsoft.ACE.OLEDB.16.0 RRS feed

  • Question

  • 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;

    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

    Sunday, January 27, 2019 3:42 PM

All replies