1 2 Previous Next 17 Replies Latest reply: Jul 19, 2012 11:06 AM by Tomeo RSS

    Connection setup from MSSQL through Oracle 11.2g

    Tomeo
      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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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-Oracle
                      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
                        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-Oracle
                          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
                            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
                              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
                                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
                                  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
                                    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