Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

Excel import to SQL Server using distributed queries

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...