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

sql-server-2005 openrowset msdasql
StevenMcD picture StevenMcD · Sep 1, 2009 · Viewed 96.5k times · Source

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?

Answer

Rajesh picture Rajesh · Jul 16, 2010

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.

Related questions

Import from csv (into different columns) via Openrowset and Microsoft.ACE.OLEDB.12.0

I want to clarify how I could import data from .csv into table with 3 columns (see CR Ranking.csv below). My query: select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Work\;HDR=Yes;', 'SELECT * FROM [CR Ranking.…

Read More
Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More