0 Replies Latest reply on Apr 5, 2013 10:40 AM by 1001264

    Oracle11g(64bit) link to SQL Server 2012


      I am trying to establish link from my Oracle 11g 64 Bit server to SQL server 2012, following are the steps followed as of yet and its not working.

      Operating system = windows server 2008 R2

      Steps are followed as given in Oracle guide as well as verified across different blog-posts.

      step 1- Created ODBC system DSN from "From the Start menu, choose Administrative Tools> select ODBC." then SQL Server DSN is created and tested successfully. DSN="SQLSRV"

      step 2 - Created file "initSQLSRV.ORA" with below configuration at path = Oracle11g\product\11.2.0\dbhome_1\hs\admin
      step 3 - updated listener.ora with below configuration
          (DESCRIPTION =
             (ADDRESS = (PROTOCOL = TCP)(HOST = myserv.myoffice.com)(PORT = 1523))
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = SQLSRV)
          (ORACLE_HOME = D:\Oracle11g\product\11.2.0\dbhome_1)
          (PROGRAM = hsodbc)
          (ENVS = "LD_LIBRARY_PATH = D:\Oracle11g\product\11.2.0\dbhome_1\LIB")
      step 4 - updated tnsnames.ora with below configuration
      SQLSRV =
          (ADDRESS_LIST =
             (ADDRESS = (PROTOCOL = TCP)(HOST = myserv.myoffice.com)(PORT = 1523))
          (CONNECT_DATA =
                (SID = SQLSRV)
           (HS = OK)
      step 5 - restarted listener with bellow commands

      1. lsnrctl reload mslistener
      2. lsnrctl status mslistener
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserv.myoffice.com)(PORT=1523)))
      STATUS of the LISTENER
      Alias                     mslistener
      Version                   TNSLSNR for 64-bit Windows: Version - Production
      Start Date                04-APR-2013 18:55:50
      Uptime                    0 days 20 hr. 59 min. 4 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   D:\Oracle11g\product\11.2.0\dbhome_1\network\admin\listener.ora
      Listener Log File         d:\oracle11g\product\11.2.0\dbhome_1\log\diag\tnslsnr\myserv\mslistener\alert\log.xml
      Listening Endpoints Summary...
      Services Summary...
      Service "SQLSRV" has 1 instance(s).
        Instance "SQLSRV", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
      3. lsnrctl service mslistener
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserv.myoffice.com)(PORT=1523)))
      Services Summary...
      Service "SQLSRV" has 1 instance(s).
        Instance "SQLSRV", status UNKNOWN, has 1 handler(s) for this service...
            "DEDICATED" established:0 refused:0
               LOCAL SERVER
      The command completed successfully
      step 6 - Now I try ti tnsping the SQLSRV SID and I get below error message
      Used parameter files:
      TNS-03505: Failed to resolve name
      step 7 - when I try to test the service(SQLSRV) via NET Manager utility, I get below error-
      ORA-12518: TNS:listener could not hand off client connection
      step 8 - The detail error in the log file is -
       05-APR-2013 15:58:44 * (CONNECT_DATA=(SID=SQLSRV)(SERVER=DEDICATED)(CID=(PROGRAM=D:\Oracle11g\product\11.2.0\dbhome_1\jdk\jre\bin\java.exe)(HOST=myserv)(USER=152803))) * (ADDRESS=(PROTOCOL=tcp)(HOST= * establish * SQLSRV * 12518
      TNS-12518: TNS:listener could not hand off client connection
       TNS-12560: TNS:protocol adapter error
        TNS-00530: Protocol adapter error
         64-bit Windows Error: 2: No such file or directory
      Well, From what i have read, there are couple of things that might be the key problem

      1. hsodbc might not work on 64bit (is it?)
      2. there is already one active SID in oracle hence I need to change the SID using oradim utility (please confirm)

      I appreciate any help on this matter.