This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jul 19, 2012 9:06 AM by Tomeo RSS

Connection setup from MSSQL through Oracle 11.2g

Tomeo Newbie
Currently Being Moderated
Hi folks,

I would like to query some data from MS SQL database through Oracle. In other forum I was pointed to Heterogenous Services, but unfortunatelly I'm having a problem with an initial set up.

Documentation I check:
http://docs.oracle.com/cd/E11882_01/server.112/e11050/admin.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14232/admin.htm

Here is my scenario:
On the same server we are running Oracle database (11g release 11.2) and MS SQL (Microsoft SQL Server Express Edition (64-bit)).

Oracle database:
tnsnames.ora
# tnsnames.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_dev\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

DEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DEV)
    )
  )


HELIOS = 
   (DESCRIPTION=
       (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
       (CONNECT_DATA = 
           (SERVICE_NAME = HELIOSDB)
       )
       (HS = OK)
    )
listener.ora
# listener.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_dev\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\Oracle

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=HELIOSDB)
         (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
         (PROGRAM=dg4odbc)
      )
  )
I'm not sure whether a listener part is configured correctly.

Also, what next should be done?
Where is defined a connection to MS SQL database?

Please guide me with this initial configuration if possible.

Kind regards,
Tomas
  • 1. Re: Connection setup from MSSQL through Oracle 11.2g
    kgronau Guru
    Currently Being Moderated
    The listener is correct.
    The connection to the Sql Server is defined in the gateway init file initHELIOSDB.ora has to be generated in :\Oracle\product\11.2.0\dbhome_dev\hs\admin. This file requires the HS_FDS_CONNECT_INFO parameter which oints to an ODBC SYSTEM DSN which connects to your SQL Server.

    Details can be found also in these 2 notes:
    How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on 64bit Windows Operating Systems to Connect to Non-Oracle Databases Post Install          [Document 1266572.1]     
    and
    How to Configure DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit to Connect to Non-Oracle Databases Post Install          [Document 466225.1]     

    Both notes are available through the My Oracle Support portal.
  • 2. Re: Connection setup from MSSQL through Oracle 11.2g
    Tomeo Newbie
    Currently Being Moderated
    Thanks for quick reply.

    Ok, so a config file initHELIOSDB.ora need to be created manualy.

    Do we have any example how could this file be configured?

    Meanwhile I'll check documents you mentioned in your post.

    Tomas
  • 3. Re: Connection setup from MSSQL through Oracle 11.2g
    kgronau Guru
    Currently Being Moderated
    There's a default example called initdg4odbc.ora in this directory which gives you the minimum settings.

    Common settings I use are:
    HS_FDS_CONNECT_INFO=MSSQLSERVER_118 # uses the system ODBC DSN MSSQLSERVER_118

    #HS_FDS_TRACE_LEVEL=255 # is commonly commented out and only used when I need to debug
    HS_FDS_SUPPORT_STATISTICS=FALSE # and i also switch of the MS SQL Server statistics usage

    HS_NLS_NCHAR=UCS2 # is used as all NCHAR data at the SQL server side is stored using UCS2 char set

    Now it depends on the Oracle database - when I have a UNICODE Oracle datase I commonly set these additional parameters:
    HS_LANGUAGE=american_america.al32utf8
    HS_FDS_REMOTE_DB_CHARSET=WE8MSWIN1252

    When I use an 8bit Oracle database I set the HS_LANGUAGE to the collation of the SQL Server:
    HS_LANGUAGE=american_america.we8mswin1252
  • 4. Re: Connection setup from MSSQL through Oracle 11.2g
    Tomeo Newbie
    Currently Being Moderated
    Thanks for reply.

    Ok, I've created initHELIOSDB.ora file in .../hs/admin folder
    # 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 = HELIOSDB
    #HS_FDS_TRACE_LEVEL = <trace_level>
    
    
    #
    # Environment variables required for the non-Oracle system
    #
    #set <envvar>=<value>
    Then I've defined a "System DSN" called HELIOSDB through Windows ODBC Data Source Administrator pointing to MS SQL Database
    based on http://docs.oracle.com/cd/B28359_01/gateways.111/b31043/configodbc.htm

    After this configuration restarted listener and then connected to db as sys user to create a public link.
    Connected to:
    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
    
    SQL> conn sys as sysdba
    Enter password:
    Connected.
    SQL> CREATE PUBLIC DATABASE LINK heliosdb CONNECT TO dbuser IDENTIFIED BY dbuserpwd USING 'HELIOSDB';
    
    Database link created.
    
    SQL> select * from dual@heliosdb;
    select * from dual@heliosdb
                       *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve the connect identifier specified
    Am I missing something ?
  • 5. Re: Connection setup from MSSQL through Oracle 11.2g
    kgronau Guru
    Currently Being Moderated
    have a look at your tnsnames.ora - it contains the alias: HELIOS
    but in your database link you're using HELIOSDB.
    CREATE PUBLIC DATABASE LINK heliosdb CONNECT TO dbuser IDENTIFIED BY dbuserpwd USING 'HELIOSDB';

    There's also another issue with the username and password is not surrounded by double quotes. You need to surround the username and passwords by double quotes as the SQL Server username and passwords are case sensitive and to preserver the case the strings need to be surrounded by double quotes.

    The syntax for your db link would be:
    CREATE PUBLIC DATABASE LINK heliosdb CONNECT TO "dbuser" IDENTIFIED BY "dbuserpwd" USING 'HELIOS';
  • 6. Re: Connection setup from MSSQL through Oracle 11.2g
    Tomeo Newbie
    Currently Being Moderated
    SQL> drop public database link heliosdb;
    
    Database link dropped.
    
    SQL>
    SQL>
    SQL> CREATE PUBLIC DATABASE LINK heliosdb CONNECT TO "dbuser" IDENTIFIED BY "dbuserpwd" USING 'HELIOS';
    
    Database link created.
    
    SQL> select * from dual@HELIOS;
    select * from dual@HELIOS
                       *
    ERROR at line 1:
    ORA-02019: connection description for remote database not found
    we are getting closer :)
  • 7. Re: Connection setup from MSSQL through Oracle 11.2g
    kgronau Guru
    Currently Being Moderated
    the name of the database link remains heliosdb only the tns alias in the using clause has changed.
    CREATE PUBLIC DATABASE LINK heliosdb....
    So your select is: select * from dual@HELIOSDB;
  • 8. Re: Connection setup from MSSQL through Oracle 11.2g
    Tomeo Newbie
    Currently Being Moderated
    Works like a charm!
    SQL> select * from dual@HELIOSDB;
    
    D
    -
    X
    
    SQL> select count(1) from TabCisOrg@HELIOSDB;
    
      COUNT(1)
    ----------
          2316
    Many thanks for your help!

    Tomas
  • 9. Re: Connection setup from MSSQL through Oracle 11.2g
    kgronau Guru
    Currently Being Moderated
    As I just saw this select:
    select count(1) from TabCisOrg@HELIOSDB;

    It looks like your SQL Server objects are not case sensitive, but when you access a different server they might be. Keep in mind to surround SQL Server objects (table, column, view names etc) for case sensitive servers by double quotes as only the double quotes will preserve the case of the object name:
    select count(1) from "TabCisOrg"@HELIOSDB;
  • 10. Re: Connection setup from MSSQL through Oracle 11.2g
    Tomeo Newbie
    Currently Being Moderated
    One more thing, as I made some modifications and maybe I skrewed it up :(

    I've added 2 more SQL databases, so I modified my files as follows:
    # tnsnames.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_dev\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.
    
    DEV =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = DEV)
        )
      )
    
    
    HELIOS = 
       (DESCRIPTION=
           (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
           (CONNECT_DATA = 
               (SERVICE_NAME = HELIOSDB)
           )
           (HS = OK)
        )
        
    HELIOSSRO = 
       (DESCRIPTION=
           (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
           (CONNECT_DATA = 
               (SERVICE_NAME = HELIOSSRO)
           )
           (HS = OK)
        )
    
    HELIOSFO = 
       (DESCRIPTION=
           (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
           (CONNECT_DATA = 
               (SERVICE_NAME = HELIOSFO)
           )
           (HS = OK)
        )            
    listener
    # listener.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_dev\network\admin\listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
        )
      )
    
    ADR_BASE_LISTENER = C:\Oracle
    
    SID_LIST_LISTENER=
      (SID_LIST=
          (SID_DESC=
             (SID_NAME=HELIOSDB)
             (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
             (PROGRAM=dg4odbc)
          )
      )
    
    SID_LIST_LISTENER=
      (SID_LIST=
          (SID_DESC=
             (SID_NAME=HELIOSSRO)
             (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
             (PROGRAM=dg4odbc)
          )
      )
    
    SID_LIST_LISTENER=
      (SID_LIST=
          (SID_DESC=
             (SID_NAME=HELIOSFO)
             (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
             (PROGRAM=dg4odbc)
          )
      )  
    created 2 init files initHELIOSSRO.ora and initHELIOSFO.ora

    Configured ODBC for each database, like in previous example

    Created 2 db links:
    CREATE PUBLIC DATABASE LINK heliossro CONNECT TO "usersro" IDENTIFIED BY "sropwd" USING 'HELIOSSRO';
    CREATE PUBLIC DATABASE LINK heliosfo CONNECT TO "userfo" IDENTIFIED BY "fopwd" USING 'HELIOSFO';
    but when I run query ORA-28545 occures
    SQL> select * from dual@HELIOSDB;
    select * from dual@HELIOSDB
                       *
    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 HELIOSDB
    
    
    SQL> select * from dual@HELIOSSRO;
    select * from dual@HELIOSSRO
                       *
    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 HELIOSSRO
  • 11. Re: Connection setup from MSSQL through Oracle 11.2g
    Tomeo Newbie
    Currently Being Moderated
    Ok, found an answer, problem was with an listener.ora
    # listener.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_dev\network\admin\listener.ora
    # Generated by Oracle configuration tools.
    
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
        )
      )
    
    ADR_BASE_LISTENER = C:\Oracle
    
    SID_LIST_LISTENER=
      (SID_LIST=
          (SID_DESC=
             (SID_NAME=HELIOSDB)
             (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
             (PROGRAM=dg4odbc)
          )
          (SID_DESC=
             (SID_NAME=HELIOSSRO)
             (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
             (PROGRAM=dg4odbc)
          )
          (SID_DESC=
             (SID_NAME=HELIOSFO)
             (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
             (PROGRAM=dg4odbc)
          )
      )
      
    Now it works like it should:
    SQL> select count(1) from TabCisOrg@HELIOSSRO;
    
      COUNT(1)
    ----------
          2443
    
    SQL> select count(1) from TabCisOrg@HELIOSFO;
    
      COUNT(1)
    ----------
           342
  • 12. Re: Connection setup from MSSQL through Oracle 11.2g
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    yes, that's the way the listener should be configured - multiple 'SID_DESC' entries for each gateway instance.
    If you decide to use something like DG4MSQL you could also use the same listener, just use a different program name, for example -

    Ok, found an answer, problem was with an listener.ora

    # listener.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_dev\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.AGT.local)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER = C:\Oracle

    SID_LIST_LISTENER=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=HELIOSDB)
    (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
    (PROGRAM=dg4odbc)
    )
    (SID_DESC=
    (SID_NAME=HELIOSSRO)
    (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
    (PROGRAM=dg4odbc)
    )
    (SID_DESC=
    (SID_NAME=HELIOSFO)
    (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
    (PROGRAM=dg4odbc)
    )
    (SID_DESC=
    (SID_NAME=DG4MSQL_1)
    (ORACLE_HOME=C:\Oracle\product\11.2.0\dbhome_dev)
    (PROGRAM=dg4msql)
    )
    )

    and also configure a gateway init file and a tnsnames.ora entry.

    Regards,
    Mike
  • 13. Re: Connection setup from MSSQL through Oracle 11.2g
    Tomeo Newbie
    Currently Being Moderated
    Thanks for corrections.

    One more problem occured when data are being queried:
    SQL> select h."CisloOrg", h."Nazev"   from "TabCisOrg"@HELIOSSRO h;
     
       CisloOrg Nazev
    ----------- --------------------------------------------------------------------------------
              0 AG TRANSPORT, s.r.o.
              1 Michal Varnavčin - SAQIA Zlín CZ
              2 Mitas a. s.
              3 AMIPOL Zlín s.r.o.
              4 EUROFINN, spol.s.r.o.
              5 Josef Císař, s. r. o.
    When I add any other non MS SQL related column, like sysdate, then a result for varchar2 fields is empty or contains strange characters.

    It could be related to encodings between databases.

    Any idea?
  • 14. Re: Connection setup from MSSQL through Oracle 11.2g
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    Could you give us an example of the query and the results ?
    What is the character set used by the SQL*Server database and by the Oracle database ? For the Oracle database what is the output from -

    select * from nls_database_parameters ;

    Regards,
    Mike
1 2 Previous Next

Legend

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