This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Sep 27, 2012 12:28 AM by kgronau RSS

Connecting to SQL Server 2008 from Oracle 11G R2

xarg-Xrc Explorer
Currently Being Moderated
My Database
Oracle XE 11GR2 (Windows x64)

Connecting to
SQL Server 2008 R2

OS: Windows 7 (both database are in same machine)

I have followed the instructions here: http://www.databasejournal.com/features/oracle/article.php/10893_3442661_2/Making-a-Connection-from-Oracle-to-SQL-Server.htm

I can TNS ping my new link in tnsnames.ora however when I run queries against the database link I get this error:

+SQL Error: 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 MYSQLSERVERDSN
28545. 0000 - "error diagnosed by Net8 when connecting to an agent"
*Cause:    An attempt to call an external procedure or to issue SQL
to a non-Oracle system on a Heterogeneous Services database link
failed at connection initialization. The error diagnosed
by Net8 NCR software is reported separately.
*Action:   Refer to the Net8 NCRO error message.  If this isn't clear,
check connection administrative setup in tnsnames.ora
and listener.ora for the service associated with the
Heterogeneous Services database link being used, or with
'extproc_connection_data' for an external procedure call.
DESC d0.resc@mysqlserverdsn
ERROR:+


Given the article is dated 2004, it probably wasn't a good idea following it as those concept may not work with 11G database. Then I came across Oracle Database Gateway, but have no idea how where to download the OUI which sets it up.

Would anyone know any good articles or links which highlights step by step how to accomplish setting up a DB link between SQL Server and Oracle Database? Cheers.
  • 1. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    The article you refer to is for HSODBC which was desupported a long time agao and which was replaced by the Database Gateway for ODBC (DG4ODBC) in 11g.
    To configure DG4ODBC on Windows 64-bit have a look at this note in My Oracle Support -

    How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems (Doc ID 1266572.1)

    The latest version is 11.2.0.3 which is available from My Oracle Support as -

    Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

    - logon to My Oracle Support
    - go to Patches & Updates section
    - search for patch number 10404530 and your platform
    - you may need to press the '+' button to see the platform list
    - click on the patch number
    - from the download screen you only need to download the following for the gateways -

    p10404530_112030_platform_5of7.zip

    - this is the Gateway media pack and has everything needed for a standalone gateway install.
    - unzip the file and run the installer and choose the gateway you want.

    It would be better to install it in a separate ORACLE_HOME from any existing installs.

    Regards,
    Mike
  • 2. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    I forgot to add that 10g HSODBC was never available on Windows 64-bit platforms.

    Regards,
    Mike

    Edited by: mkirtley on Sep 20, 2012 9:21 AM
  • 3. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    xarg-Xrc Explorer
    Currently Being Moderated
    thanks mkirtley,

    Was the zip file to download: p10404530_112030_MSWIN-x86-64_5of7.zip ?

    When I put the patch & platform (Microsoft Window x64) it gave a long list of file to download in a popup like interface. Cheers.
  • 4. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    Yes, that is all you need for the gateways on Windows 64-bit -

    Oracle Gateways p10404530_112030_platform_5of7.zip

    as it details in the readme, so for Windows 64-bit you need the zip file you have named -

    p10404530_112030_MSWIN-x86-64_5of7.zip

    10404530 is called a paptchset but it is actually a full release. The gateway zip file has everything needed for a standalone install, so install in a separate OH from any other installs. This makes maintenance and configuration easier.

    Regards,
    Mike
  • 5. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    xarg-Xrc Explorer
    Currently Being Moderated
    Cheers mate, I'm giving it a try. Wasted so much time following that 8 year old article.
  • 6. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    Okay, let us know what happens.
    We have no control over non-Oracle sites so can't ensure they are up to date or have valid information unlike My Oracle Support - support.oracle.com - where we keep the information up to date and relevant.

    Regards,
    Mike
  • 7. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    xarg-Xrc Explorer
    Currently Being Moderated
    Hi Mike, I opened up Windows Console >> ODBC Data Source Administrator, but where would I enter the SID_DESC, PROGRAM etc. as below? There is a long list of Drivers.

    When I pick Oracle for XE I get the option as image below:

    http://i.imgur.com/0bjDF.jpg
    0) ODBC System DSN
    Please open the ODBC Administrator and configure a SYSTEM DSN choosing your ODBC driver and following its onscreen dialog.
    1) The listener needs a new SID entry like the following:
    (SID_DESC =
    (PROGRAM = dg4odbc)
    (SID_NAME = dg4odbc)
    (ORACLE_HOME = D:\oracle\product\11.2.0)
    )
    Document Display https://support.oracle.com/epmos/faces/ui/km/SearchDocDisplay.jspx...
  • 8. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    When setting up the System DSN for DG4ODBC to use then you need to select the ODBC driver needed to connect to the non-Oracle database, not an Oracle ODBC driver. In your case it would use a SQL*Server ODBC driver.
    The screen shot shows you already have a DSN using the 'SQL Server' driver so you could use that for the HS_FDS_CONNECT_INFO parameter in the gateway init<sid>.ora if it connects to the SQL*Server database you want. If not, then create a new one connecting to the SQL*Server database to which you want to connect.

    Regards,
    Mike
  • 9. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    xarg-Xrc Explorer
    Currently Being Moderated
    Hi mkirtley,

    I've followed that article from Oracle Support and still bogged down. I have installed the gateway via the OUI, and did the following:

    Step 0: Already done and tested as with screenshot

    Step 1: I created my entry as below in C:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora and bounced the listener with lsnrctl reload and pinged dg4odbc. The result was ok.
    # listener.ora Network Configuration File: C:\product\11.2.0\tg_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.
    
    #LISTENER =
    #  (DESCRIPTION_LIST =
    #    (DESCRIPTION =
    #      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    #      (ADDRESS = (PROTOCOL = TCP)(HOST =AA-PC)(PORT = 1522))
    #    )
    #  )
    
    LISTENER =
         (DESCRIPTION_LIST =
         (
              DESCRIPTION =
              (ADDRESS_LIST =
                   (
                   ADDRESS = (PROTOCOL = TCP)(HOST = AA-PC) (PORT = 1521))
                   )     
              )  
         )
    
    ADR_BASE_LISTENER = C:\product\11.2.0\tg_1
    
    SID_LIST_LISTENER =
         (SID_LIST =
         (SID_DESC =
         (PROGRAM = dg4odbc)
         (SID_NAME = dg4odbc)
         (ORACLE_HOME = C:\product\11.2.0\tg_1)
         )
    )
    Step 2: tnsnames.ora in Oracle Home where DB in installed: C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora
    XE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = AA-PC)(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) 
        ) 
      )
      
    dg4odbc =
       (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=AA-PC)(PORT=1521))
        (CONNECT_DATA=(SID=dg4odbc))
        (HS=OK)
     )
    But here it wasn't clear if I should edit it here or in C:\oraclexe\app\oracle\product\11.2.0\server\hs\admin\tnsnames.ora, I did in both places.


    Also updated sqlnet.ora in C:\product\11.2.0\tg_1\NETWORK\ADMIN\sqlnet.ora
    SQLNET.AUTHENTICATION_SERVICES= (NTS)
    NAMES.DEFAULT_DOMAIN = AA-PC
    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
    3. Created the file initdg4odbc.ora in C:\product\11.2.0\tg_1\hs\admin which has the following:
    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO = MYSQLSERVERDSN
    HS_FDS_TRACE_LEVEL = off
    Created the database link in SYS schema but still got that net8 error as above.
  • 10. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    orafad Oracle ACE
    Currently Being Moderated
    Orion Starc wrote:
    bounced the listener with lsnrctl reload and pinged dg4odbc. The result was ok.
    Tnsping does not test services, only listener port response.

                   ADDRESS = (PROTOCOL = TCP)(HOST = AA-PC) (PORT = 1521))
    Tg_1 home's listener should be set to a port not used by XE listener, e.g 1522.

    C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora
    dg4odbc =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=AA-PC)(PORT=1521))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
    )
    And this entry in xe home should match with tg listener's port number etc.

    Test by select * from dual@nameofdblink;

    Created the database link in SYS schema but still got that net8 error as above.
    In general, never use SYS for such purposes. Create objects as your own dba-type user, or in your application schema.
  • 11. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    xarg-Xrc Explorer
    Currently Being Moderated
    Thanks orafad I've made those changes now, restarted listener. Still no luck. Is there anything else that I can check?

    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 SQLSERVER
    28545. 0000 - "error diagnosed by Net8 when connecting to an agent"
    *Cause:    An attempt to call an external procedure or to issue SQL
    to a non-Oracle system on a Heterogeneous Services database link
    failed at connection initialization. The error diagnosed
    by Net8 NCR software is reported separately.
    *Action:   Refer to the Net8 NCRO error message.  If this isn't clear,
    check connection administrative setup in tnsnames.ora
    and listener.ora for the service associated with the
    Heterogeneous Services database link being used, or with
    'extproc_connection_data' for an external procedure call.
    Error at Line: 6 Column: 19
  • 12. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    kgronau Guru
    Currently Being Moderated
    Please provide a summary of your listener services: lsnrctl status l

    - Klaus
  • 13. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    orafad Oracle ACE
    Currently Being Moderated
    Also post PATH setting and make sure, via Process Monitor, what dg4odbc executable is actually loaded (since you should have two on the host - one in XE and another in the "tg" 11.2 database install).
  • 14. Re: Connecting to SQL Server 2008 from Oracle 11G R2
    xarg-Xrc Explorer
    Currently Being Moderated
    C:\Windows\system32>lsnrctl status
    
    LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 25-SEP-2012 10:00
    :05
    
    Copyright (c) 1991, 2010, Oracle.  All rights reserved.
    
    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 - Produ
    ction
    Start Date                21-SEP-2012 16:29:39
    Uptime                    3 days 17 hr. 30 min. 26 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Default Service           XE
    Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\a
    dmin\listener.ora
    Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\AA-PC\liste
    ner\alert\log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AA-PC)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=AA-PC)(PORT=8081))(Presentati
    on=HTTP)(Session=RAW))
    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 "XEXDB" has 1 instance(s).
      Instance "xe", status READY, has 1 handler(s) for this service...
    Service "xe" has 1 instance(s).
      Instance "xe", status READY, has 1 handler(s) for this service...
    The command completed successfully
1 2 Previous Next

Legend

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