How to connect to Oracle database from SQL Server 2012 using linked server?

1
votes

I want to connect my SQL Server 2012 to Oracle 11g database using Linked server and Oracle client. I have installed SQL Server 2012 in my local machine. Oracle database installed in client machine.

My client has shared me ip address of machine, System ID, Instance number and Oracle database user ID and password.

I have followed below mentioned steps:

  1. I have installed SQL Server 2012 in my machine
  2. I have installed Oracle client 11g in my machine
  3. I have also installed ODT with ODAC _32bit

  4. I have created one linked server with below script steps

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

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

exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//HOSTNAME/TRQ', 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'ORACLE USERNAME', @rmtpassword='ORACLEPASSWORD'

When I execute below query getting error:

OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". Msg 7303, Level 16, State 1, Line 9 Cannot initialize the data source object of OLE DB provider "ORAOLEDB.Oracle" for linked server "MyOracle".

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

1
sql-serveroracle
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. – halfer

1 Answers

0
votes

1- Add ODAC dir and ODAC\Bin to the Path (Check it with SET PATH command)

2- Be sure that you Oracle and SQL version are bit-compatible (Both are 32-bit ot both are 64-bit)

3- Be Sure that ODAC is bit-compatible with them.

4- If the port of your Oracle instance is not 1521, include it after Host IP: