3 Replies Latest reply: Apr 17, 2013 6:58 AM by 1003598 RSS

    create a database link to access SqlServer 2005 view from oracle 11gr2

    Karan Kukreja
      Hi All,

      Greetings for the day.

      Though I have seen quite a few posts but just wanted to cross-verfiy and double check hence posting.

      Oracle Database : 11gr2
      OS :SOLARIS SPARC 64 Bit

      SQL Server Database : Sql Server 2005
      OS : Windows 2003 32 Bit

      The requirement is to access a view residing in the SQL Server 2005 Database using the Oracle Database. This involves creation of a Database link in the Oracle Database to access the SQL Server.

      Can you suggest which approach should i follow ( I am able to understand that both do the same job ):
      How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install [ID 562509.1]
      How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install [ID 561033.1]
      Will they do the same job ? And can they be used on any OS and version of the SQL Server Database as version information for SQL Server is not specified anywhere?

      Please let me know so that I can it forward with Business for approvals and budgetting.

        • 1. Re: create a database link to access SqlServer 2005 view from oracle 11gr2
          Hi KK,
          in general they are doing the same, they allow you to connect to an SQL Server from your Oracle database.
          But there are differences in the licensing and in the capability of each gateway.
          As you mentioned some notes here I assume you have access to the My Oracle Support portal and I will first give you 2 other notes:
          Database Gateway and Generic Connectivity (DG4ODBC) Licensing Considerations     
          Functional Differences Between Database Gateway for ODBC (DG4ODBC) and Specific Database Gateways (DG4MSQL, dg4sybase, dg4informix, dg4teradata)

          These notes describe the major differences between both products. DG40dbc is for free but would require a 3rd party ODBC driver which might cause additional costs when you purchase it from a commercial ODBC vendor like DataDirect. DG4MSQL comes with everything you need and is ready to use once you installed and configured it.
          Now the major functional difference: DG4ODBC is the free product and works with every suitable 3rd party ODBC driver. You can connect to text files or to other foreign databases like DB2 or MS SQL Server. So DG4ODBC is a generic connectivity and it only knows a couple of functions it can directly map to foreign database functions. All other functions will cause a post processing which means that all records from the remote table(s) will be fetched into the Oracle database and the result is then processed locally. So you see its capability is limited.
          DG4MSQL is designed for MS SQl Server connections. It knows how to map a lot of Oracle functions to the SQL Server equivalents and it allows you also to participate in distributed transactions as well as calling remote SQL Server stored procedures.
          So best would be first to check what you need to do; for just selecting data from a MS SQl Server both gateways would work. Then you have to check if there are available ODBC drivers for free or if you have to purchase them from a 3rd party vendor for DG4ODBC (for example when you install DG4ODBC on MS Windows or Linux x86-64bit) you could use the ODBC drivers from Microsoft. If you have to purchase a driver for your Solaris platform you might compare the pricing of DG4MSQL and the 3rd party ODBC driver license.

          Best regards,
          • 2. Re: create a database link to access SqlServer 2005 view from oracle 11gr2
            Karan Kukreja
            Hi Klaus,

            I had a similar understanding.. but your words have added weight to it ! Thanks a lot for your comment.. :)

            • 3. Re: create a database link to access SqlServer 2005 view from oracle 11gr2
              Your question is similar to the post: Oracle 11gr2 connection to Sql Server using dg4msql problem

              Please visit: http://stackoverflow.com/questions/4658942/oracle-11gr2-connection-to-sql-server-using-dg4msql-problem

              Answer on the website:

              You seem to be using the Gateway for MySQL set-up rather than the Heterogeneous Gateway (for ODBC connections). Here is an overview of the process

              On SQL Server create a database user and give it read access to the database/tables you want to read via the Oracle database link.

              In the gateway home each SQL Server database you want to access should have an init.ora located in $OH/dg4msql/admin in the form initsid.ora where sid is the name of the database to be used in the link (e.g. initbob.ora), so create one

              You must now add the new sid to the listener.ora in the gateway home using an additional SID_DESC section inside the existing SID_LIST, for example

              (SID_DESC =
              You should now stop and restart the gateway listener so that the new sid becomes active. NB a reload is not enough.

              You must now add the new sid in the tnsnames.ora file for the listener of each database in which you will create a link. You don't need to do this in the gateway home unless it is also a database home in which you will create a database link.

              bob =
              (DESCRIPTION =
              (ADDRESS = (PROTOCOL = TCP)(HOST = severname.example.com)(PORT = 1690))
              (CONNECT_DATA = (SID = bob))
              (HS = OK)
              NB: The host and port are for the gateway not for the SQL Server database

              In each database that requires a link to the MS-SQL database you should create a database link to your new gateway sid.

              CONNECT TO "ms_user" IDENTIFIED BY "ms-passwd" USING 'bob';
              where ms-user and ms-password are the SQL Server user you created right at the start.

              Now you can test the new database link

              SELECT COUNT(*) FROM "Table_Name"@bob;
              Once you have this working you can alter the initsid.ora file to add parameters to suit your connection. If you do it this way you can easily add and manage many different databases via the gateway.


              Edited by: 1000595 on 04:58 17-04-2013