3 Replies Latest reply: Mar 1, 2013 10:46 AM by Mkirtley-Oracle RSS

    oracle 10g to sql server 2005 - how to setup

    babulmiah82
      Hi,

      I have been searching on how to setup a connection from oracle 10g to sql server 2005 and i have to admit i am struggling a bit.

      I have read about Heterogeneous Services and Database Gateway but we really dont want to fork for licence fees so it looks like i have to investigate HS.

      My environment is:

      10gR2 Linux 32 bit
      SQL Server 2005 32bit.

      Would someone be able to provide me with a list of steps to help me get this setup?

      Thanks.

      B
        • 1. Re: oracle 10g to sql server 2005 - how to setup
          Mkirtley-Oracle
          Hi,
          If you don't want to pay for any licence fees then you will have to use the 11g Database Gateway for ODBC (DG4ODBC) which is included in your RDBMS license.
          You need to use the 11g versions because all previous gateway versions have been desupported for some time. The latest version is 11.2.0.3 which can be downloaded from My Oracle Support as -

          Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

          and download -
          p10404530_112030_platform_5of7.zip
          - this is the Gateway media pack and has everything needed for a standalone gateway install.

          This version is certified the following RDBMS versions - 10.1.0.5 + RDBMS patch 5965763, 10.2.0.3 + RDBMS patch 5965763, 10.2.0.4, 10.2.0.5, all 11.1 versions, all 11.2 versions.
          You have 2 choices about where you can install it and it would be better to install the gateway into a new and separate ORACLE_HOME from any existing Oracle installs -

          1. On the Linux 32-bit platform where the RDBMS is running. If you install it here you will need to supply a third party ODBC driver. The only free one I am aware of is from FreeTDS but there may be others. There can be problems with the FreeTDS so another one may be preferable but these usually require a license fee.
          See this note in My Oracle Support -

          How to Configure DG4ODBC on Linux x86 32bit or on HP-UX RISC (DG4ODBC 11.1 only) to Connect to Non-Oracle Databases post install (Doc ID 466228.1)

          2. Install DG4ODBC on the Windows platform where SQL*Server is running. If you install on Windows then you will be able to use the Microsoft SQL*Server ODBC driver which should already be installed. See this note on My Oracle Support -

          How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit (Doc ID 466225.1)

          If you don't have access to My Oracle Support you will need to contact someone in your organisation to access the notes for you or review the gateway documentation -

          http://www.oracle.com/pls/db112/homepage

          Regards,
          Mike
          • 2. Re: oracle 10g to sql server 2005 - how to setup
            babulmiah82
            Hi,

            Our application is not supported on 11g, any instructions on 10gR2?

            Thanks.

            B
            • 3. Re: oracle 10g to sql server 2005 - how to setup
              Mkirtley-Oracle
              Hi,
              Even if your application is not supported on 11g you should still be able to use the 11g gateway. As I said earlier the 11.2.0.3 gateway is certified with the following RDBMS versions - 10.1.0.5 + RDBMS patch 5965763, 10.2.0.3 + RDBMS patch 5965763, 10.2.0.4, 10.2.0.5, all 11.1 versions, all 11.2 versions.
              The connection using the gateway is made by database links in the RDBMS so it doesn't matter which gateway you use as long as it is certified with the RDBMS on which your application is running. The gateway link is transparent to the application and the application will not know if you are using a 10g or 11g gateway.
              All the 10g gateways have been desupported for several years now. If you did want to install and use a 10g version you will need to open a SR to request the relevant 10g software as it is no longer available for online download.

              Regards,
              Mike