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.

/u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora

LISTENER_11G =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = g1u0872.wordpress.com)(PORT = 1526))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))

)

)

SID_LIST_LISTENER_11G=

(SID_LIST=

(SID_DESC=

(SID_NAME=dg4msql)

(ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1)

(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.1.0/db_1/dg4msql/driver/lib:/u01/app/oracle/product/11.1.0/db_1/lib)

(PROGRAM=dg4msql)

)

)

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

/u01/app/oracle/product/11.1.0/db_1/dg4msql/admin/initdg4msql.ora

# 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

#

HS_FDS_CONNECT_INFO=cceap0016.wordpress.com:2048//Crystal

# alternate connect format is hostname/serverinstance/databasename

HS_FDS_TRACE_LEVEL=ODBC

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

HS_FDS_SUPPORT_STATISTICS=FALSE

#Added for performance improvment

HS_RPC_FETCH_SIZE=50000

HS_ROWID_CACHE_SIZE=10000

HS_LONG_PIECE_TRANSFER_SIZE=1MB

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.

/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

CRYSTAL_ITG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = g1u0872.wordpress.com)(PORT = 1526))

(CONNECT_DATA =

(SID = dg4msql))

(HS=OK)

)

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;

COUNT(*)

———-

562

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

D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora

PROVT =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = g1u0854.wordpress.com)(PORT = 1525))

)

(CONNECT_DATA =

(SID = PROVT)

)

)

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.

Advertisements

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 (9.2.0.7) {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
    i.e
    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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: