Tuesday, 24 June 2014

Heterogeneous Services – Connectivity Between Oracle to SQLSERVER


                Install 10g or 11g oracle (software only) in SQLSERVER DB system.
Then

In  $ORACLE_HOME/hs/admin/ directory
Create file like initSQLDB.ora

Add below lines in that file

HS_FDS_CONNECT_INFO = SQLDB
HS_FDS_TRACE_LEVEL = OFF

Add listener entry as below in listener.ora file of SID_LIST:
/*10G
 (SID_DESC =
(SID_NAME = SQLDB)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = hsodbc)
                                )
/* 11G
 (SID_DESC =
(SID_NAME = SQLDB)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = dg4odbc)
)






Create a odbc connection for SQLSERVER as below


 In system DSN add new Data Source Name By Selecting ADD Button



Choose Sqlserver driver







Add tns entry in Oracle Database server tnsnames.ora file

SQLCONN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = IPADDRESS)(PORT = PORT))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =SQLDB)
    )
    (HS=OK)
  )

Now, Create DBLink Oracle databse to connect SQLSERVER

Create database link mssqldb_link connect to sa identified by pass123 using ‘SQLCONN’;

Now we are Ready with our Heterogeneous connection Between Oracle and Sqlserver

we can   test  our connectivity


Select  from table@mssqldb_link;