1 2 Previous Next 19 Replies Latest reply: Sep 27, 2012 2:28 AM by Kgronau-Oracle RSS

    Connecting to SQL Server 2008 from Oracle 11G R2

    xarg-Xrc
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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-Oracle
                                Please provide a summary of your listener services: lsnrctl status l

                                - Klaus
                                • 13. Re: Connecting to SQL Server 2008 from Oracle 11G R2
                                  orafad
                                  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
                                    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