14 Replies Latest reply: Aug 8, 2012 7:05 AM by 752126 RSS

    Connecting SQL server from Oracle 11g

    Sam.G
      Hello,
      I know there are several threads available to this post but please help me in
      *step by step process to follow in connecting 2 servers.
      I tried everything in internet but got Connection closed error after tnsping of DNS name. Do i need to run any instance sort of thing in SQL server?
      I dont have any access on SQL server.Whatever i have to do that has to be only in Oracle server.I got all those information and created ODBC (using SQL server driver) and tested the connection successfully in Oracle server. Altered INIT*.ORA,TNSNAMES.ORA and LISTENER.ORA but when pinging TNS awfully getting the afore said error.

      My environment : Oracle 11g server - SQL server 2008 ( both in windows platform).

      My
      TNS names entry


      GSIS =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 63455))
      (CONNECT_DATA = (SID=GSIS)
      (SERVER = DEDICATED)
      (HS=OK)
      )
      )


      Listener entry


      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
      )
      )

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\bin\oraclr11.dll")
      )
      (SID_DESC =
      (SID_NAME = GSIS)
      (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
      (PROGRAM = hsodbc)
      )
      )


      and

      initGSIS


      entry


      # 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 = GSIS
      HS_FDS_TRACE_LEVEL = OFF


      #
      # Environment variables required for the non-Oracle system
      #
      #set


      **************************************************
      when tnsping i am getting

      TNS connection closed.

      I am awfully stuck here.

      Please guide..
        • 1. Re: Connecting SQL server from Oracle 11g
          Kgronau-Oracle
          There are a couple of issues:

          Listener.ora:
          (SID_DESC =
          (SID_NAME = GSIS)
          (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
          (PROGRAM = hsodbc)

          in 11.2 the program executable is called DG4ODBC
          so it loks like:
          (SID_DESC =
          (SID_NAME = GSIS)
          (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
          (PROGRAM = dg4odbc)


          Once you have changed the listener you should STOP and START it in the command line window using lsnrctl: lsnrctl stop then lsnrctl start and the service summary should then show for GSIS 1 service handler in status unknwon.


          the GSIS tns entry
          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 63455))
          does not point to your Oracle listener
          (ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
          and the HS key word is not placed coerrectly:
          GSIS =
          (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
          (CONNECT_DATA = (SID=GSIS))
          (HS=OK)
          )

          Best would be to check out this note available on My Oracle Support portal:
          For 32bit Windows:
          How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install          [Document 466225.1]     
          and for 64bit Windows:
          Document 1266572.1 How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install
          • 2. Re: Connecting SQL server from Oracle 11g
            Mkirtley-Oracle
            Hi,
            There are several problems with your configuration files.

            1. Listener.ora - you have -


            SID_LIST_LISTENER =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME = CLRExtProc)
            (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
            (PROGRAM = extproc)
            (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\bin\oraclr11.dll")
            )
            (SID_DESC =
            (SID_NAME = GSIS)
            (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
            (PROGRAM = hsodbc)
            )
            )

            but it should be -

            SID_LIST_LISTENER =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME = CLRExtProc)
            (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
            (PROGRAM = extproc)
            (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\bin\oraclr11.dll")
            )
            (SID_DESC =
            (SID_NAME = GSIS)
            (ORACLE_HOME = C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1)
            (PROGRAM = dg4odbc)
            )
            )

            For 11g the program is dg4odbc and not hsodbc.

            2. tnsnames - you have -

            GSIS =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.1)(PORT = 63455))
            (CONNECT_DATA = (SID=GSIS)
            (SERVER = DEDICATED)
            (HS=OK)
            )
            )

            but it should be -

            GSIS =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
            (CONNECT_DATA = (SID=GSIS)
            (SERVER = DEDICATED)
            (HS=OK)
            )
            )


            the host and port are those of the listener not of SQL*Server.

            Make the changes, stop and start the gateway listener and try again.

            Regards,
            Mike
            • 3. Re: Connecting SQL server from Oracle 11g
              Kgronau-Oracle
              Mike,
              the HS keyword is also not correct ... see my posting above ...
              • 4. Re: Connecting SQL server from Oracle 11g
                Sam.G
                Hello Mike,
                Thank you for replying.
                The SQL server's IP adress is 10.1.1.1 ( for instance) with port number 63455.
                Oracle database address is COB417147.cob.apac.bosch.com.
                What should i do now?

                Thanks again.

                Regards,
                Sam.
                • 5. Re: Connecting SQL server from Oracle 11g
                  Kgronau-Oracle
                  check out my first posting and correct your listener as the executable is still wrong. The provide the listener start output generated from lsnrctl
                  • 6. Re: Connecting SQL server from Oracle 11g
                    Sam.G
                    Hello ,
                    The above said corrections are made and still shows the same error.
                    Do i need to do something in SQL server while pinging TNS?

                    Where to find the note?

                    How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install [Document 466225.1]

                    Kindly guide.

                    Thanks for understanding,
                    Regards,
                    Sam.
                    • 7. Re: Connecting SQL server from Oracle 11g
                      Kgronau-Oracle
                      please provide the lsnrctl status output and also the tnsping output.

                      BTW, did you install dg4odbc 32 or 64bit and what is the word size of your operating system?
                      • 8. Re: Connecting SQL server from Oracle 11g
                        Sam.G
                        Status

                        C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\BIN>lsnrctl status

                        LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-MAY-2012 17:48
                        :14

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

                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
                        STATUS of the LISTENER
                        ------------------------
                        Alias LISTENER
                        Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
                        ction
                        Start Date 11-MAY-2012 17:36:30
                        Uptime 0 days 0 hr. 11 min. 48 sec
                        Trace Level off
                        Security ON: Local OS Authentication
                        SNMP OFF
                        Listener Parameter File C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\network\
                        admin\listener.ora
                        Listener Log File c:\oracle\app\amk1cob\diag\tnslsnr\COB417147\listener\
                        alert\log.xml
                        Listening Endpoints Summary...
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=COB417147.cob.apac.bosch.com)(PORT=1
                        522)))
                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=COB417147.cob.apac.bosch.com)(PORT=8
                        080))(Presentation=HTTP)(Session=RAW))
                        Services Summary...
                        Service "CLRExtProc" has 1 instance(s).
                        Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
                        Service "GSIS" has 1 instance(s).
                        Instance "GSIS", status UNKNOWN, has 1 handler(s) for this service...
                        Service "orcl.cob.apac.bosch.com" has 1 instance(s).
                        Instance "orcl", status READY, has 1 handler(s) for this service...
                        Service "orclXDB.cob.apac.bosch.com" has 1 instance(s).
                        Instance "orcl", status READY, has 1 handler(s) for this service...
                        The command completed successfully


                        TNS ping

                        C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\BIN>tnsping GSIS

                        TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 11-MAY-2
                        012 18:21:55

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

                        Used parameter files:
                        C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


                        Used TNSNAMES adapter to resolve the alias
                        Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.
                        1.1)(PORT = 63455)) (CONNECT_DATA = (SID=GSIS)) (HS=OK))
                        TNS-12537: TNS:connection closed

                        C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\BIN>


                        BTW it is 32-bit machine.

                        Edited by: 933729 on May 11, 2012 5:57 AM
                        • 9. Re: Connecting SQL server from Oracle 11g
                          Mkirtley-Oracle
                          Hi,
                          You still need to change your tnsnames.ora entry.
                          As said earlier it should look like this -

                          GSIS =
                          <space>(DESCRIPTION =
                          <space>(ADDRESS = (PROTOCOL = TCP)(HOST = COB417147.cob.apac.bosch.com)(PORT = 1522))
                          <space>(CONNECT_DATA = (SID=GSIS))
                          <space>(HS=OK)
                          <space>)

                          it needs to refer to the listener host and port, not the SQL*Server host and port.
                          I've also added <space> to represent there should be at least one space at the beginning of each line, as they get lost in the posts.
                          You don't need to do anything on the SQL*Server. The gateway communicates with it using the System DSN.

                          Please make the change and let us know what happens.

                          Regards,
                          Mike

                          Edited by: mkirtley on May 11, 2012 2:09 PM
                          • 10. Re: Connecting SQL server from Oracle 11g
                            Sam.G
                            Hi,

                            Thank you Mike. It is working fine i.e) TNS is pinging finally by altering it to host's address in TNS.
                            Please clarify how it refers SQL server without giving any entries anywhere regarding SQL credentials. I have created DSN.Does this do that job?
                            I have created DBlink

                            create database link GSIS_PQSR
                            connect to USER_NAME identified by PASSWORD using 'GSIS';

                            select * from t_pmap_test_for_int@GSIS;

                            Now i am getting ORA-02019: connection description for remote database not found

                            Please guide.

                            Thank you,

                            Regards,

                            Sam.

                            Edited by: 933729 on May 11, 2012 6:17 AM
                            • 11. Re: Connecting SQL server from Oracle 11g
                              Mkirtley-Oracle
                              Sam,
                              The connection between DG4ODBC and SQL*Server is handled by the information and driver specified in the system DSN defined as the HS_FDS_CONNECT_INFO parameter.
                              When you create the database link you should double quotes round the user and password as SQL*Server is case sensitive. If quotes are not used they are translated to uppercase, so try -

                              create database link GSIS_PQSR
                              connect to "USER_NAME" identified by "PASSWORD" using 'GSIS';

                              in the correct case as defined on SQL*Server.

                              For the ora-2019 check that -

                              - initGSIS.ora is in the directory C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\hs\admin
                              - that the tnsnames.ora entry is in the directory $ORACLE_HOME\network\admin for the RDBMS not the C:\Oracle\app\amk1cob\product\11.2.0\dbhome_1\network\admin for the gateway.

                              Regards,
                              Mike
                              • 12. Re: Connecting SQL server from Oracle 11g
                                Sam.G
                                Hello Mike,

                                Got it!
                                You are the best in business.
                                Hats off to you.
                                With out your help nothing could have been done.
                                Thank you once again for your precious comments.

                                Regards,
                                Sam.
                                • 13. Re: Connecting SQL server from Oracle 11g
                                  Mkirtley-Oracle
                                  Hi Sam,
                                  That's good, glad you got it working and thanks for the feedback.

                                  Regards,
                                  Mike
                                  • 14. Re: Connecting SQL server from Oracle 11g
                                    752126
                                    Hi Mike,

                                    I am raising my question in this thread becuase through this thread and replies I have sucessfuly established the connectivity between Oracle 11G and SQl Server 2008.

                                    Thanks a lot for the help, but now I have 1 issue I am not able to call or execute the SP's or function of sql server in oracle 11G.

                                    Can you please suggest or help me.

                                    Thanks

                                    Regards
                                    Anant.