This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jul 24, 2013 12:59 PM by Dird RSS

Hetrogeneoues connection between Oracle and SQL server

ManishS137 Newbie
Currently Being Moderated

Hi ,

 

I am having Issues in Making Heterogeneous  DB link between Oracle Xe and sql server .

 

Here is the scenario-

My Oracle and ms SQL 2003 are running on same machine(windows 2003) ,

 

I have followed this link and reached till 5 but I am not able to tnsping the SQLSERVER DNS entry .

http://www.databasejournal.com/features/oracle/article.php/3442661/Making-a-Connection-from-Oracle-to-SQL-Server.htm

 

Below are my lintener and tnsfiles

 

 

LISTENER.ORA

===========================================================================================================================================================

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

      (PROGRAM = extproc)

       (ENVS = "EXTPROC_DLLS=ONLY:C:\oraclexe\app\oracle\product\11.2.0\server\bin\OraClr11.dll")

    )

   )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

  )

 

LISTENERSQL =

(ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))

      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

    )

   )

 

 

SID_LIST_LISTENERSQL=

  (SID_LIST=

      (SID_DESC =

       (SID_NAME = SQLSRVRDBLNK)

       (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

       (PROGRAM = hsodbc)

      )

      )

 

 

===========================================================================================================================================================

TNSNAMES.ORA

 

 

XE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = testenviroment)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )

 

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

 

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

 

 

 

SQLSRVRDBLNK  =

  (DESCRIPTION=

(ADDRESS=  (PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

(CONNECT_DATA=(SID=SQLSRVRDBLNK)

  (HS=OK)

   )

  )

========================================================================================================================================================

Listner Status

 

 

 

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>lsnrctl status LISTENERSQL

 

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 19-JUL-2013 13:15:59

 

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

 

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))

STATUS of the LISTENER

------------------------

Alias                     LISTENERSQL

Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production

Start Date                19-JUL-2013 12:17:39

Uptime                    0 days 0 hr. 58 min. 24 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora

Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\testenviroment\listenersql\alert\log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))

Services Summary...

Service "SQLSRVRDBLNK" has 1 instance(s).

  Instance "SQLSRVRDBLNK", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

 

=========================================================================================================================================================

 

Now If I try to Tnsping SQL heterogeneous connection , i get

 

 

 

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN>tnsping SQLSRVRDBLNK

 

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 19-JUL-2013 13:17:47

 

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

 

Used parameter files:

C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

 

 

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION=

TNS-12533: TNS:illegal ADDRESS parameters

 

 

Need Help , please suggest

Thanks,

Manish

  • 1. Re: Hetrogeneoues connection between Oracle and SQL server
    Asif Muhammad Guru
    Currently Being Moderated

    Hi Manish,

     

    What is the name of the SYSTEM odbc that you have created.

     

    Best regards,

  • 2. Re: Hetrogeneoues connection between Oracle and SQL server
    ManishS137 Newbie
    Currently Being Moderated

    Hi Asif ,

     

    Thanks for the reply ,

    I have named the ODBC as SQLSRVRDBLNK.

     

    Thanks,

    Manish

  • 3. Re: Hetrogeneoues connection between Oracle and SQL server
    EdStevens Guru
    Currently Being Moderated

    928666 wrote:

     

    Hi ,

     

    I am having Issues in Making Heterogeneous  DB link between Oracle Xe and sql server .

     

    Here is the scenario-

    My Oracle and ms SQL 2003 are running on same machine(windows 2003) ,

     

    I have followed this link and reached till 5 but I am not able to tnsping the SQLSERVER DNS entry .

    http://www.databasejournal.com/features/oracle/article.php/3442661/Making-a-Connection-from-Oracle-to-SQL-Server.htm

     

    Below are my lintener and tnsfiles

     

     

    LISTENER.ORA

    ===========================================================================================================================================================

    SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

          (SID_NAME = PLSExtProc)

          (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

          (PROGRAM = extproc)

        )

        (SID_DESC =

          (SID_NAME = CLRExtProc)

          (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

          (PROGRAM = extproc)

           (ENVS = "EXTPROC_DLLS=ONLY:C:\oraclexe\app\oracle\product\11.2.0\server\bin\OraClr11.dll")

        )

       )

     

    LISTENER =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

        )

      )

     

    LISTENERSQL =

    (ADDRESS_LIST=

          (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))

          (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

        )

       )

     

     

     

    You are unnecessarily complicating things by having two listeners defined -- LISTENER and LISTENERSQL.  Checking the status requires two seperate commands -- 'lsnrctl status listener' and 'lsnrctl status listenersql'.

     

     

    Only one listener is required.  Only one port for that listener is required.

     

     

    And listening for connections only from/to 'localhost' is usually not a good idea. 'localhost' means 'this here machine I'm executing on.'  Every computer on the planet is 'localhost'.  When a client asks the network to route a request to 'localhost', the request never leaves the machine from which it originates.

  • 4. Re: Hetrogeneoues connection between Oracle and SQL server
    ManishS137 Newbie
    Currently Being Moderated

    Hi ,

     

     

    Thanks for the update I have changed the Listener as suggested , now my Listener File is

     

     

    SID_LIST_LISTENER =

      (SID_LIST =

        (SID_DESC =

          (SID_NAME = PLSExtProc)

          (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

          (PROGRAM = extproc)

        )

        (SID_DESC =

          (SID_NAME = CLRExtProc)

          (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

          (PROGRAM = extproc)

        )

        (SID_DESC =

           (SID_NAME = SQLSRVRDBLNK)

           (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

           (PROGRAM = hsodbc)

           (ENVS = "EXTPROC_DLLS=ONLY:C:\oraclexe\app\oracle\product\11.2.0\server\bin\OraClr11.dll")

          )

       )

      

    LISTENER =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS_LIST=

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.146)(PORT = 1521))

          (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))

          (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))

        )

      )

    ========================================================================================================================

     

    and tns entry 

     

    SQLSRVRDBLNK  =

      (DESCRIPTION=

    (ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))

    (CONNECT_DATA=(SID=SQLSRVRDBLNK)

      (HS=OK)

       )

      )

    ========================================================================================================================

    Restarted the listener

     

    C:\Documents and Settings\Administrator>lsnrctl start LISTENER

     

    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 20-JUL-2013 12:27:38

     

    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     

    Starting tnslsnr: please wait...

     

    TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production

    System parameter file is C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora

    Log messages written to C:\oraclexe\app\oracle\diag\tnslsnr\testenviroment\listener\alert\log.xml

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.146)(PORT=1521)))

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))

     

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

    STATUS of the LISTENER

    ------------------------

    Alias                     LISTENER

    Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production

    Start Date                20-JUL-2013 12:27:44

    Uptime                    0 days 0 hr. 0 min. 5 sec

    Trace Level               off

    Security                  ON: Local OS Authentication

    SNMP                      OFF

    Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora

    Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\testenviroment\listener\alert\log.xml

    Listening Endpoints Summary...

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.146)(PORT=1521)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))

    Services Summary...

    Service "CLRExtProc" has 1 instance(s).

      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Service "PLSExtProc" has 1 instance(s).

      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Service "SQLSRVRDBLNK" has 1 instance(s).

      Instance "SQLSRVRDBLNK", status UNKNOWN, has 1 handler(s) for this service...

    The command completed successfully

     

    ========================================================================================================================

    Still Getting the same Error

     

    C:\Documents and Settings\Administrator>tnsping SQLSRVRDBLNK

     

    TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 20-JUL-2013 12:27:52

     

    Copyright (c) 1997, 2010, Oracle.  All rights reserved.

     

    Used parameter files:

    C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\sqlnet.ora

     

     

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION=

    TNS-12533: TNS:illegal ADDRESS parameters

     

    C:\Documents and Settings\Administrator>

    ========================================================================================================================

     

     

    Do we need to change any thing in sqlnet.ora file for heterogeneous connection?

     

     

    Thanks ,

    Manish

  • 5. Re: Hetrogeneoues connection between Oracle and SQL server
    ManishS137 Newbie
    Currently Being Moderated

    Thanks,

     

    The problem is solved ,

     

    I just changed

    SQLSRVRDBLNK  =

      (DESCRIPTION=

    (ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))

    (CONNECT_DATA=(SID=SQLSRVRDBLNK)

      (HS=OK)

       )

      )

     

    to

     

    SQLSRVRDBLNK =(DESCRIPTION=(ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))(CONNECT_DATA=(SID=SQLSRVRDBLNK)(HS=OK)))

     

    and now i am able to tnsping the sql dns.

     

    Thanks,

    Manish

  • 6. Re: Hetrogeneoues connection between Oracle and SQL server
    mkirtley-Oracle Expert
    Currently Being Moderated

    Manish,

        The tnnames.ora entry you posted is still incorrect.
    You said you changed it to -

     

    SQLSRVRDBLNK =(DESCRIPTION=(ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))(CONNECT_DATA=(SID=SQLSRVRDBLNK)(HS=OK)))

     

    but it should be in the format -

     

    SQLSRVRDBLNK =

       (DESCRIPTION=

         (ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))

         (CONNECT_DATA=(SID=SQLSRVRDBLNK)

          )

           (HS=OK)

        )

     

    That is, the (HS=OK) should be outside the 'CONNECT_DATA' entry.

     

    Regards,

    Mike

  • 7. Re: Hetrogeneoues connection between Oracle and SQL server
    ManishS137 Newbie
    Currently Being Moderated

    Thanks mike ,

    by doing changes before , i was able to tnsping SQL servers DNS ,

    Now after moving (HS=OK) out of CONNECT_DATA i am getting following error

     

    SQL> select * from dbo.spt_monitor@SODBL;

    select * from dbo.spt_monitor@SODBL

                                  *

    ERROR at line 1:

    ORA-28545: error diagnosed by Net8 when connecting to an agent

    Unable to retrieve text of NETWORK/NCR message 65535

    ORA-02063: preceding 2 lines from SODBL

     

    Regards,

    Manish

  • 8. Re: Hetrogeneoues connection between Oracle and SQL server
    kgronau Guru
    Currently Being Moderated

    Did you see my update in the other thread?

    In 11g the executable is called dg4odbc, not hsodbc. Also please remove the ENVS parameter, it is not needed for listener.ora

     

    So please change your listener.ora file SID

        (SID_DESC =

           (SID_NAME = SQLSRVRDBLNK)

           (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

           (PROGRAM = dg4odbc)

          )

       )

     

    Once done, please stop and start the listener.

     

    - Klaus

  • 9. Re: Hetrogeneoues connection between Oracle and SQL server
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi Manish,

         The listener summary shows -

     

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.146)(PORT=1521)))

     

    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))

     

    but in the tnsnames.ora you have -

     

      (ADDRESS=  (PROTOCOL= TCP)(HOST=192.168.60.146)(PORT=1522))

     

    Either change the port to 1521 or in listener.ora change -

     

    LISTENER =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS_LIST=

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.146)(PORT = 1521))

          (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))

          (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))

        )

      )

     

    to -

     

    LISTENER =

      (DESCRIPTION_LIST =

        (DESCRIPTION =

          (ADDRESS_LIST=

          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.60.146)(PORT = 1521))

          (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))

          (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.146)(PORT=1522)))

        )

      )

     

    You also need to change the gateway entry from -

     

        (SID_DESC =

           (SID_NAME = SQLSRVRDBLNK)

           (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

           (PROGRAM = hsodbc)

           (ENVS = "EXTPROC_DLLS=ONLY:C:\oraclexe\app\oracle\product\11.2.0\server\bin\OraClr11.dll")

          )

     

    to -

     

        (SID_DESC =

           (SID_NAME = SQLSRVRDBLNK)

           (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)

           (PROGRAM = dg4odbc)

          )

     

    You do not need the 'ENV' parameter for Windows and the executable in 11.2 is called dg4odbc.

     

    Stop and start the listener after making the changes.

     

    Regards,

    Mike

  • 10. Re: Hetrogeneoues connection between Oracle and SQL server
    ManishS137 Newbie
    Currently Being Moderated

    Hi Mike ,

     

    I have done the changes as described , Now the error is

     

     

    SQL> select * from dbo.spt_monitor@SODBL;

    select * from dbo.spt_monitor@SODBL

                                  *

    ERROR at line 1:

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

    ORA-28541: Error in HS init file on line 7.

    ORA-02063: preceding 2 lines from SODBL

     

    Regards,

    Manish

  • 11. Re: Hetrogeneoues connection between Oracle and SQL server
    mkirtley-Oracle Expert
    Currently Being Moderated

    Manish,

      Can you post the initSQLSRVRDBLNK.ora file ?
    It should be in C:\oraclexe\app\oracle\product\11.2.0\server\hs\admin.

     

    Regards,

    Mike

  • 12. Re: Hetrogeneoues connection between Oracle and SQL server
    ManishS137 Newbie
    Currently Being Moderated

    Hi Mike ,

     

    Please find my initSQLSRVRDBLNK.ora file , from C:\oraclexe\app\oracle\product\11.2.0\server\hs\admin. location

     

    # This is a sample agent init file that contains the HS parameters that are

    # needed for the Database Gateway for ODBC

     

    #

    # HS init parameters

    #

    HS_FDS_CONNECT_INFO = <odbc data_source_name>

    HS_FDS_TRACE_LEVEL = <trace_level>

     

    HS_FDS_CONNECT_INFO = SQLSRVRDBLNK

    HS_FDS_TRACE_LEVEL = OFF

     

    #

    # Environment variables required for the non-Oracle system

    #

    #set <envvar>=<value>

    set ODBCINI=C:\WINDOWS\ODBC.ini

  • 13. Re: Hetrogeneoues connection between Oracle and SQL server
    mkirtley-Oracle Expert
    Currently Being Moderated

    Manish,

      Comment out the lines -

     

    HS_FDS_CONNECT_INFO = <odbc data_source_name>

    HS_FDS_TRACE_LEVEL = <trace_level>

     

    and remove the 'set ODBCINI....'

     

    If you have access to My Oracle Support look at this note -

     

    How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit (Doc ID 466225.1)

     

     

    Regards,

    Mike

  • 14. Re: Hetrogeneoues connection between Oracle and SQL server
    ManishS137 Newbie
    Currently Being Moderated

    Hi Mike ,

    I have done as you said ,and also following the metalink ID 466225.1.

     

    Now the Error that I am getting is

     

    SQL> select * from dbo.spt_monitor@SODBL;

    select * from dbo.spt_monitor@SODBL

                                  *

    ERROR at line 1:

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or

    access denied. {08001,NativeErr = 17}[Microsoft][ODBC SQL Server Driver][TCP/IP

    Sockets]ConnectionOpen (Connect()). {01000,NativeErr = 10061}

    ORA-02063: preceding 2 lines from SODBL

     

     

     

    Thakns ,

    Manish

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points