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

Question

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 1

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.

Answer 2

I had to download and install "Microsoft Access Database Engine 2010 Redistributable" available here.

"The cause of this issue is that there is no 64-bit ODBC text driver installed on your 64-bit Windows server 2003 actually. The 64-bit MSDASQL just provides an OLEDB/ODBC 'bridge' that allows applications built on OLEDB and ADO (which uses OLEDB internally) to access data sources through ODBC drivers." Source

Answer 3

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.

Answer 4

/* Linked server between local(Client) SQL server and Remote SQL server 2005*/

USE master
GO
-- To use named parameters: Add linked server in the source (Local machine - eg: MachineName or LocalSeverLoginName)

sp_addlinkedserver 
 @server = N'LnkSrv_RemoteServer_TEST', 
 @srvproduct=N'', -- Leave it blank when its not 'SQL Server'
 @provider=N'SQLNCLI', -- see notes
 @datasrc=N'RemoteServerName', 
 @provstr=N'UID=sa;PWD=sa;'
 --,@catalog = N'MYDATABASE' eg: pubs
GO

/*
 Note: 
  To check provider name use the folling query in the destination server
   Select Provider From sys.servers
*/
----------------------------------------------------------------------------------------------------------
-- Optional
--EXEC sp_addlinkedsrvlogin 'LnkSrv_RemoteServer_TEST', 'true' -- (self is true) -- for LocalSeverLoginName
--GO

-- Remote login
sp_addlinkedsrvlogin
 @rmtsrvname = 'LnkSrv_RemoteServer_TEST',
 @useself = 'False',
 @rmtuser = 'sa',
 @rmtpassword = 'sa'
GO

-- OR
/*
IF the above add linked server login failed then try in the Linked Server (LnkSrv_RemoteServer_TEST) Property 
Select -> Security - > 'For a login not defined in the list above, Connection will:'

Choose - > Be made using this security context
SET Remote login: sa
With password: sa
*/
----------------------------------------------------------------------------------------------------------

-- Test server connection
declare @srvr nvarchar(128), @retval int;
set @srvr = 'LnkSrv_RemoteServer_TEST';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );

-- OR

BEGIN TRY 
    EXEC sp_testlinkedserver N'LnkSrv_RemoteServer_TEST'; 
END TRY 
BEGIN CATCH 
    PRINT 'Linked Server not available'; 
    RETURN; 
END CATCH 
----------------------------------------------------------------------------------------------------------

-- Get access linked server database
SET xact_abort ON 
GO

BEGIN TRANSACTION
SELECT  *  FROM LnkSrv_RemoteServer_TEST.DBName.dbo.tblName 
COMMIT TRAN
GO

-- OR
SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, 'SELECT * FROM DBName.dbo.tblName')
GO

-- OR
SELECT * FROM OPENQUERY(LnkSrv_RemoteServer_TEST, 'SELECT * FROM sys.databases Order by name')
GO
----------------------------------------------------------------------------------------------------------

Answer 5

We ended up restarting the database server and that seemed to solve the problem. Maybe the files were getting locked somehow. We'll never know for sure though

Answer 6

This issue happened to me, as well. A combination of enabling the "Allow inprocess" provider option for the OraOLEDB.Oracle provide (SSMS > Server Objects > Linked Servers > Provides > OraOLEDB.Oracle), restarting the SQL Server Windows service and lastly adjusting the permissions on the TNSNAMES.ora file directly.

Answer 7

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