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

user_38595 0
Viewed 104727

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?

msdasql openrowset sql-server-2005
7 Answers

I think you made an ODBC using a 32bit driver but SQL Server Management Studio requires 64 bit. Because this has happened when you want to create a linked server for MYSQL on SQL Server. So, you should make the DSN System use for 64bit drivers

user_132993360

Another option is to add an SQL Server login that uses SQL Server authentication and log in using that account. You'll need to go to Security in the server instance properties and select SQL Server and Windows Authentication mode and may need to make sure that the necessary roles are assigned to that user.

user_191629140
Related