mysql的ole db 访问接口msdasql的数据源对象,无法初始化OLE DB提供程序“ MSDASQL”的数据源对象。用于链接服务器“（null）”...

最新推荐文章于 2021-08-11 20:03:07 发布

陈书画

最新推荐文章于 2021-08-11 20:03:07 发布

阅读量2.2k

点赞数

文章标签： mysql的ole db 访问接口msdasql的数据源对象

当尝试通过OpenRowset在SQL Server 2005中从服务器上的Excel文件读取数据时，遇到'无法初始化数据源对象'的错误。问题在于SQL Server服务运行用户的Temp文件夹权限不足。解决方法是调整Temp文件夹的安全设置，确保查询运行时的凭证可以访问。重新创建DSN以避免权限冲突，无需重启即可解决问题。

摘要由CSDN通过智能技术生成

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.

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?

解决方案

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.