使用分布式查询将Excel导入到SQL Server [英] Excel import to SQL Server using distributed queries

问题描述

只要在运行脚本时使用的用户凭据是sysad，我就可以在本地和生产环境中成功进行设置。

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.

此网址（http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm）谢尔盖·瓦瑟连科（Sergey Vaselenko）很有帮助

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

它已经启动并运行了几个月。突然之间，在上周的某个时候，我收到了一位使用该上传程序的用户的投诉，他们无法再上传任何excel文件。

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.

链接服务器（null）的OLE DB提供程序 Microsoft.ACE.OLEDB.12.0
返回了消息未指定错误。消息7303，级别16，状态1，
第1行无法初始化链接服务器（空）的OLE DB提供程序
Microsoft.ACE.OLEDB.12.0的数据源对象。

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 has updated the server nor my local.

使用脚本的不同应用程序的所有服务器都遇到错误。

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

我什至无法在我自己的本地PC上运行它。（当我在本地使用JET作为驱动程序时，它仍然可以工作，但是不能使用ACE驱动程序运行它）

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)

我真的已经对ace驾驶员发生了什么感到困惑？

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

我开始怀疑是否有来自Microsoft的任何更新后端或ace驱动程序上的某种错误？

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.

推荐答案

可能是此潜在问题：

发生此问题，因为登录帐户没有对SQL Server启动帐户的临时文件夹的完全
访问权限。
链接服务器查询在登录帐户的上下文中运行。如果
运行链接服务器查询，则SQL Server会尝试在SQL Server
启动帐户的临时文件夹中创建一个临时文件
数据源名称（DSN）。

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.

这篇关于使用分布式查询将Excel导入到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！