Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)"

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?

like image 983
StevenMcD Avatar asked Sep 01 '09 12:09

StevenMcD


People also ask

Can not initialize the datasource object of OLE DB provider?

It means you need to create the same user name and password on your two servers if you use one sql server authentication. If the answer is helpful, please click "Accept Answer" and upvote it.

What is Msdasql provider?

MSDASQL is Microsoft's Object Linking and Embedding Database (OLE DB) provider for Open Database Connectivity (ODBC) drivers. MSDASQL allows OLE DB consumer applications to use ODBC drivers to connect to a data source.


1 Answers

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.

like image 51
Rajesh Avatar answered Sep 21 '22 15:09

Rajesh