How to connect Oracle and Sql server

Method 1) Oracle connect to Sql server

Using Oracle Gateway in 11g, if the database version is 10g, patch 5965763 should be installed on it.

Step1: Install Oracle gateway at an existing Oracle 11g home or a new home, and configure listener, oracle gateway for Sql server initial file:

In our example, using new ORACLE_HOME /u01/app/oracle/product/11.1.0/db_1

1) listener.


















2) init<SID>.ora of the hs subsystem


# This is a customized agent init file that contains the HS parameters

# that are needed for the Database Gateway for Microsoft SQL Server


# HS init parameters


# alternate connect format is hostname/serverinstance/databasename





#Added for performance improvment




3) Start listener

lsnrctl start LISTENER_11G

Step2. Patch existing oracle 10g database home with patch 5965763.

Step3. Configure tnsname and create db link in oracle 10g
1) Add tnsname entry.






(SID = dg4msql))



2) Create db link

Sql> Create public database link CRYSTAL_ITG
connect to PRISM identified by passwd
using ‘crystal_itg’;

Step4. Test

SQL> select count(*) from “Book”@CRYSTAL_ITG;




Method 2) Sql server connect to Oracle

Using Sql server linked server in version 2005.

Step1. Install Oracle client in windows and configure TNS names











Step2. Configure Linked server in Sql server

Follow these click steps, Connect to Sql server in Microsoft Sql Server Management Studio => Server Objects => Linked Servers => right click, New Linked Server =.>

Add default login name and password.

Step3. Test connection.


About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

10 Responses to How to connect Oracle and Sql server

  1. Ramprasad says:

    It’s a nice document sir,
    But my oracle DB is ( {HP-UX} and i want coonect to the SQL Server 2005

    where i need to mention Oracle DB hostname & port & SID
    and where i need to mention SQL server hostnames & port & DB name….
    can u give me clarity for this…
    please sir…

  2. neworacledba says:

    you post great on unique topics…keep up the good job…keep blogging more

  3. vijay says:

    The above all steps done, when executing the query it gives the following error
    ORA-00942: table or view does not exist
    [Transparent gateway for MSSQL]
    ORA-02063: preceding 2 lines from ‘crystal_itg’

  4. Al Webre says:

    How do I connect an Oracle 9i database to SQL Server 2008?

    An ODBC connection can be made in the Oracle client (using odbcad32.exe in the windows\syswowo64 directory which all works and tests successfully.

    But SQL Server Import and Export Wizard does not see it. It seems to be using the 32-bit odbcad32.exe in the windows\system32 directory which only lists SQL Server and SQL Server Native Client drivers.

    How do I fix this?

  5. read more says:

    That was really a great blog! I read something similar within a science blogging site.
    Worthwhile of looking into.

  6. fun says:

    I relish, lead to I found just what I used to be having a look for.
    You’ve ended my 4 day lengthy hunt! God Bless you man. Have a great day. Bye

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: