0

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

Improve this question
2
  • When you say fresh install do you mean on the client machine or server ? If the client do you have a client with the old software and does it work there ?
    – armitage
    Jun 21, 2021 at 20:56
  • hey. it's my own machine. I formatted it and i got a sql server 2019. I tried everything that I can think of to make it work. as a sql dba, i fixed this error several times, but this time I can't find light.
    – Racer SQL
    Jun 22, 2021 at 10:55

2 Answers 2

Reset to default
1

The first error message seems to indicate the SQL Server cannot access the source .xlsx file. Perhaps the account being used by SQL Server does not have access to the file because of filesystem rights, or perhaps the .xlsx is already opened exclusively by some other app, such as Excel.

Also, for a new installation you probably want to change the provider name to 'Microsoft.ACE.OLEDB.16.0'

Improve this answer
2
  • thanks Hannah. for some reason it worked and I have no idea what I did. why should I change the provider from 12 to 16? just an upgrade? its working with 12 but not with 16.
    – Racer SQL
    Jun 22, 2021 at 10:54
  • I was guessing that you probably had the latest version of the driver installed.
    – Hannah Vernon ♦
    Jun 22, 2021 at 12:27
0

If you want to know which version of ACE.OLEDB is installed, you just need to check the folder in the picture below:

Providers

you can also use this query to check:

EXEC sp_MSset_oledb_prop, this will list all providers as below:

Providers T-SQL

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.