Cannot initialize the data source object of ole db provider msdasql for linked server ошибка

/* 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
----------------------------------------------------------------------------------------------------------

Greetings,

I am implementing an update to an application that needs to run some SQL Server Stored Procedures queries against a legacy Access database.  I implemented a linked server for this purpose.  If I run the queries as an Administrative user, all is well.  But for limited rights users, I get the following error if I try to access any of the Access data:

«Cannot initialize the data source object of OLE DB provider «Microsoft.Jet.OLEDB.4.0» for linked server «XYZ».»

I’ve looked at any permissions related items I could find in SQL Server Management Studio 2005 (Express).  I can’t find anything different in permissions between the Users and Administrators group.  I confirmed that the query would run if I simply added the limited rights user to the Windows Administrators group.  I opened all the permissions on the directories and files where the Access .mdb file and the SQL MDF/LDF files are located with no increase in joy.

After some ‘research’ (i.e., googling), I replaced the linked server with the openrowset command, and changed the provider to MSDASQL (to get more verbose error messages).  From that arrangement, I received the following messages:

OLE DB provider «MSDASQL» for linked server «(null)» returned message «[Microsoft][ODBC Microsoft Access Driver] Disk or network error.».

OLE DB provider «MSDASQL» for linked server «(null)» returned message «[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key ‘Temporary (volatile) Jet DSN for process 0x6a4 Thread 0xc3c DBC 0x453b3fd4 Jet’.».

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

Any ideas?  I am quite stumped.

Here’s the initial setup I had for the linked server 

EXEC sp_grantlogin [BUILTINAdministrators]

EXEC sp_addsrvrolemember @loginame = [BUILTINAdministrators], @rolename = ‘sysadmin’

— Create linked server so we can use SQL Server to query the DAFIF

— Edition 7 Access database prepared by PFPS

exec sp_addlinkedserver

     @server     = ‘XYZ’,

     @srvproduct = ‘Access 97’,

     @provider   = ‘Microsoft.Jet.OLEDB.4.0’,

     @datasrc    = ‘C:XYZDBase<Access_Filename>.mdb’,

     @location   = ‘localhost’,

     @catalog    = ‘XYZ’

—The following command will fail with the «Cannot initialize …» error message

SELECT * FROM ACOM  —ACOM is a SQL view for one of the tables in the Access DB

First, install the correct Oracle drivers. You want the latest version of the Oracle Data Access Components (ODAC), and you want the XCopy deployment. They are available here:
64-bit Oracle Data Access Components (ODAC) Downloads
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

One you download and unzip this into a folder run the following command from that folder:

C:UsersdbrowneDownloadsODAC121010Xcopy_x64>.install.bat oledb c:oracleodac64 odac64 true

Then you need to add two folders to your system path:

c:oracleodac64 and c:oracleodac64bin

Then you must reboot for the system path change to be visible by services like SQL Server.

After reboot you’re ready to create and test the linked server.
First configure the Oracle OleDB provider to run inside the SQL Server process, and configure it to accept parameters.

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1

exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1

Then create the linked server definition. Instead of a TNSNames alias, use an EZConnect identifier. Here I’m specifying an IP address and a SID to connecto to an Oracle Express instance running on a VM:

exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.16.8.119/xe', N'FetchSize=2000', ''
exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'
exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'system',@rmtpassword='xxxxxx'     

Now you’re ready to test. You configured the linked server for ‘rpc out’ so we can send a simple passthrough query to test connectivity:

exec ('select 1 a from dual') at MyOracle

That’s it.

Acctivate for CYMA users may receive errors loading customer invoices after upgrading or installing SQL Server

Acctivate for CYMA users may receive an error similar to the following:

Link of CYMAdemo.AcctivateCYMAdemo..Ap_In failed due to error ExecuteNonQuery failed for Database ‘ACCTivate_demo’. —> An exception occurred while executing a Transact-SQL statement or batch. —> Cannot initialize the data source object of OLE DB provider «MSDASQL» for linked server «CYMAdemo».OLE DB provider «MSDASQL» for linked server «CYMAdemo» returned message «[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified».

If Microsoft SQL Server is installed on a separate machine than Acctivate and CYMA and Pervasive, this may lead to an issue in which SQL Server and Pervasive are unable to link together.  This would cause Acctivate users to receive the error, above, due to not being able to load data from CYMA.

The solution is rather simple.  Wherever Microsoft SQL Server is installed you will need to also install the Pervasive Client.  This loads the Pervasive ODBC Interface driver, which is used between SQL Server and Pervasive to create a linkage. 

You can download the Pervasive Client Driver from Actian or you can also install the Pervasive Client from the CYMA Installation CD.