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

问题描述

有一个有趣的问题.我正在通过Sql2005中的OpenRowset从服务器上的Excel文件中读取.我已经多次运行查询，没有任何问题.我刚刚出去开会，突然出现错误无法为链接服务器(null)"初始化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 Server服务的用户的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"的数据源对象.用于链接服务器“(null)"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！