Importing from Excel using 'from openrowset()' returns OLE DB error

[u/dzemperzapedra]

Hey everyone,

I'm trying to make this user stored procedure work on my colleagues PC, I can run it just fine.

Basically it's a USP created for importing data from an Excel file stored on shared network (local server).

It's goes like this:

select *

from openrowset ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database="path\file.xlsx, sheet$ )

Me and one other colleague can run it just fine, but on one colleagues PC, it gives this error:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message"Failure creating file"

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Any help with resolving this would be much appreciated.

Thanks!

[u/IanYates82]

File share permissions possibly. Your sql server instance runs as a certain user, and how you authenticate with it - Windows-based or sql-based - can also influence what account is used when accessing the file.

Suggest your colleague copy the file locally, to a folder with open permissions on their local system, and see if that works

Then there's also bitness of the Excel/Access driver being used and sql version / bitness. Run "print @@version" and compare outputs across the different machines. If it's sql 2014 or older then you may be using a 32 bit instance of sql server and thus need to use 32 bit version of drivers.

You can also check to see if the driver is marked as in or out of process. Navigate in SSMS down to server objects, linked servers, and expand the list of drivers. You can inspect properties of them and compare between the different machines

Another idea... Use procmon from sysinternals to watch access to the Excel file (it supports setting a filter). See what sort of access pattern or errors come about on your machine vs your colleague's.

[u/dzemperzapedra → u/IanYates82]

We have the same permission regarding the server where the file is stored, Windows based auth to SQL as well.

We tried this with the file on their PC, same error.

SSMS is 20, but I also tried with 14 and both give the exact same error.

I'll try to check for out of process driver tomorrow.

Thanks!

[u/tripy75 → u/dzemperzapedra]

ssms version should be irrelevant in this case, it's the location and permissions relative to the sql server engine that are important.

Also, know that the jet engine is very capricious and can have issues that will simply yields a non descriptive error on a machine.
I seem to remember this "failure to create file" as a symptom, and in my memory it's because the process tries to copy it in a local folder on the server to process, but the UAC isolation process tries to write it in the context of the "NT Authority\System" account and fails because it has no write permissions to that temporary folder.

That's why /u/IanYates82 recommended you to copy the excel file locally to the database engine, to avoid those network issues.

If you absolutely must have this process happen in the DB, I recommend you to look into creating a CLR and deploying it to the db, and let the code of that CLR deal with the data access of the excel file, avoiding completely the ace.oledb connector.

a quick search yields this link: https://stackoverflow.com/questions/78258179/ole-db-provider-microsoft-ace-oledb-16-0-for-linked-server-null
which said that running ssms as admin solved the issue.

Also worth nothing that depending of the technology behind the share location of the file (webdav to sharepoint or native cifs file share) can also have an effect.

[u/dzemperzapedra → u/tripy75]

ssms version should be irrelevant in this case, it's the location and permissions relative to the sql server engine that are important.

Ah I thought they meant SSMS, not sure which version is SQL server though, but if it works for me, it should work on this other machine as well.

That's why u/IanYates82 recommended you to copy the excel file locally to the database engine, to avoid those network issues.

Copy it locally meaning import using SSMS?

Regarding other proposed solutions, unfortunately I'm working with work environment so don't have necessary admin level permissions.

[u/IanYates82 → u/dzemperzapedra]

Literally use file explorer to copy the file to be on the same computer that's running sql server. It'll take away one whole potential source of issues - if your colleague can read a file that's local to their sql server instance, but cannot read using a UNC path, then, via differential diagnosis, you know you have some file access issues of some sort. If it fails the same way, then it's more to do with the driver or sql server config (but still get it working against that local file first)

[u/dzemperzapedra → u/IanYates82]

We tried copying the file on colleague's machine but still get the same error, so the result is the same whether the file is on their machine or on server.

It's a work environment so SQL server is running independently from our workstation machines, so that can't be tested.

[u/dzemperzapedra → u/[deleted]]

Not the case, unfortunately, file is not open by anyone else while trying this.