connecting SQL SERVER 2005 from Oracle

Question

hi

i am running Oracle and SQL SERVER 2oo5 on the same server. Plstform win 2003 Enterprise. i have created a System file DSN. Make the entry in listener.ora and tnsnames.ora.

but when i m tring to ping my dsn system dont responds. and when i m trying to create a database link using my dsn, it gives error.

What to do ? thanks for help in advance.

Sunil

Answer 1

You have to:

-Create the UDL file

-Configure the heterogeneus services ($ORACLE_HOME\hs\admin) and create a file like initDATAB.ora with this content:

HS_FDS_CONNECT_INFO="UDLFILE=C:\\ccc\\SQLSERVER.udl

-Add entry on listener.ora on SID_LIST_LISTENER like this:

   (SID_DESC=
      (SID_NAME=DATAB)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM=hsolesql)
   )

-Create an entry on tnsnames.ora:

SQLSERVERDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = riereta)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME=DATAB)
    )
    (HS=OK) 
  )

-Create a database link pointing to SQLSERVERDB

References:

-Oracle® Database Heterogeneous Connectivity Administrator's Guide

-Oracle® Database Net Services Reference

-CREATE DATABASE LINK on Oracle® Database SQL Reference