0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ace.spades
  • 33
  • 5

1 Answers1

0

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.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • Thanks for the tip :) But I also encountered this error even on my local pc, where my login is an administrator that have full access. Also, I have a Office 2010 and the ace driver installed on my local and on the prod servers are of version 14.0. I tried to run this same script on one of the old pc at the office whose Office is 2007 and ace driver 12.0, it was successful. So I'm thinking this has something to do with the ace 14.0, may be Microsoft have sent an update on all of the Office 2010 and ace 14.0. I'll try to send this scenario to Microsoft support. – ace.spades Oct 18 '11 at 23:48