I've been using the same query for ages to query Excel files:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel
12.0;Database=C:\_DBA_TEMP\Controle.xlsx', [planilha1$])
But with this fresh install I'm receiving an error I've never seen before:
Msg 7399, Level 16, State 1, Line 13 The OLE DB provider "Microsoft.ACE.OLEDB.16.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 13
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" 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've no idea where to start to troubleshoot this. After Googling for hours, I've only find blogs with a procedure to be created. My problem is not that (missed procedure) and I just can't find a way to fix this.
After changing SQL SERVER USER to another one that's not the default, I started receiving this error:
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
Installed data sources and still no love.
Even checking/unchecking "allow inprocess" is not solving the error. (I really don't want to mess with this option, since it can shutdown your SQL Server instance.)