1 2 Previous Next 18 Replies Latest reply: Jul 24, 2013 2:59 PM by Dird RSS

    Hetrogeneoues connection between Oracle and SQL server

    ManishS137

      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

          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

            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

              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

                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

                  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

                    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

                      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-Oracle

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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