Excel import to SQL Server using distributed queries

Question

I was able to set this up successfully on my local and on production, as long as the user credentials used on running the script is a sysad.

This url (http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm) By Sergey Vaselenko was very helpful

It's been up and running for months already. And just all of a sudden, sometime around last week, I received a complaint from one of our users who utilizes the uploader that they can no longer upload any excel files.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

No one is altering the script.

No one has updated the server nor my local.

All of the servers for different apps that uses the script encountered the error.

I can't even run it on my own local pc.(When I use JET as a driver on my local it still works, but can't run it using ACE driver)

I'm really confused already on what happened on the ace driver?

I'm starting to wonder if there have been any update backend from microsoft or some kind of a bug on the ace driver??

Hope someone could help me if you have similar issue.

Answer 1

It could be this potential prblem:

This problem occurs because the login account does not have full access to the temporary folder of the SQL Server startup account. The linked server query runs in the context of the login account. If you run a linked server query, SQL Server tries to create a temporary file data source name (DSN) in the temporary folder of the SQL Server startup account.