linked server connection error in Sql server

Question

When I tried to execute query I got the below error.

SELECT   *    
FROM OPENQUERY([TESTORACLE], 'select *   from TESTTABLE')

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TESTORACLE".

I have created DSN and used below query to create linked server.

EXEC sp_addlinkedsrvlogin 
@rmtsrvname    = 'TESTORACLE'
,@useself       = 'False'
,@locallogin    = NULL
,@rmtuser       = 'USERNAME'
,@rmtpassword   = 'PASSWORD';

EXEC sp_addlinkedserver 
@server        = 'TESTORACLE'
,@srvproduct    = 'ORCLELINK'
,@provider      = 'MSDASQL'
,@datasrc       = 'ORCLELINK'

After that I tested the linked server and it worked fine. When I execute a query it throws access denied exception so I enable Allow In process to the provider.

After that it throws above error and the test connection also not works.

Not sure why the connection fails when I enable Allow in process.

Please help me to resolve the error.

Thanks,

you should recreate the linked server with Allow in process optiion checked. Try it. — George K
– George K, Commented Aug 21, 2017 at 11:41
But enabling allow in process cause the connection issue. One thing it worked yesterday and I can communicate with Oracle server. The issue is only occurs on Today morning. not sure what happens in between. — User12345
– User12345, Commented Aug 21, 2017 at 12:12
@GeorgeK how to enable Allow in process in the creation script. please — User12345
– User12345, Commented Aug 21, 2017 at 12:13
why have not you created special provider for Oracle and use MSDASQL? — George K
– George K, Commented Aug 21, 2017 at 12:26
@GeorgeK sorry I'm a noob here. Could you please explain in detail. — User12345
– User12345, Commented Aug 21, 2017 at 12:30

George K · Accepted Answer · 2017-08-21 12:59:39Z

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:\Users\dbrowne\Downloads\ODAC121010Xcopy_x64>.\install.bat oledb c:\oracle\odac64 odac64 true

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

c:\oracle\odac64 and c:\oracle\odac64\bin

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.

I have installed both sql server and oracle on same machine. Will it work? — User12345
– User12345, Commented Aug 21, 2017 at 13:39

Stack Exchange Network

linked server connection error in Sql server

1 Answer 1

Your Answer

Hot Network Questions

linked server connection error in Sql server

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions