无法初始化 OLE DB 提供程序“MSDASQL"的数据源对象对于链接服务器“(空)"； [英] Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"

问题描述

有一个有趣的问题.我正在通过 Sql2005 中的 OpenRowset 从服务器上的 excel 文件中读取数据.我已经多次运行查询，没有任何问题.我刚刚出去参加一个快速会议，突然出现错误无法初始化链接服务器的 OLE DB 提供程序MSDASQL"的数据源对象(空)""

Having an interesting issue. I'm reading from an excel file on a server via an OpenRowset in Sql2005. I've run the query a number of times without any problems. I've just gone out for a quick meeting and suddenly I get the error "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)""

我已经确保服务器上没有使用这些文件，甚至删除了它们并将它们重新复制到服务器上，但我仍然遇到相同的错误.

I've made sure the files are not in use on the server and even deleted them and recopied them over onto the server and still I'm getting the same error.

更新:这似乎只有在我加入来自不同 openrowset 的两个选择时才会发生.如果我单独运行查询，它们仍然可以正常工作.我之前已经完成了加入，没有任何问题.想法?

UPDATE: This only seems to happen if I join two selects from different openrowsets. If I run the queries individually they still work fine. I have done the join before without any issues. Ideas?

推荐答案

出现问题是因为在运行查询的凭据下无法访问运行 SQL 服务器服务的用户的 Temp 文件夹.尝试以最少的限制设置此临时文件夹的安全性.每次运行 openrowset 查询时创建的 dsn 然后可以重新创建，而不会产生任何凭据冲突.这对我有用，没有任何重启要求.

The problem comes because the Temp folder of the User under which the SQL server service is running isn't accessible under the credentials which the query is running. Try to to set the security of this temp folder with minimal restrictions. The dsn that gets created every time you run an openrowset query then can be recreated without any credentials conflict. This worked for me without any restart requirements.

这篇关于无法初始化 OLE DB 提供程序“MSDASQL"的数据源对象对于链接服务器“(空)"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！