Oracle Database 10g DBLink with SQL Server

Database Link with SQL-Server: on the Request of Mr.Fahadi from Taif

Step 1: Define ODBC connectivity

From the start menu click on Control Panel -- Administrative Tool and double click the Data Source (ODBC) icon

Click on the System DSN tab and then click the Add button.

Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.

Name:                  MYSQLSERVER (You can Enter Your Own)

Description:        Some Description about Connection

SQL Server:         local OR Remote Address

Click Next to continue

Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select.

Click Next to continue.

Clicked Finish

Click Test Data Source to verify your Connection.

Now “MYSQLSERVER” System DNS has been created.

Step 2: Create a Heterogeneous Services

Go to following location à C:\oracle\product\10.2.0\db_1\hs\admin

Now you have to change following three files at this location

1.     inithsodbc.ora file

2.     listener.ora.sample file

3.     tnsnames.ora.sample file

 

Open inithsodbc.ora in notepad … Its look like:

# This is a sample agent init file that contains the HS parameters that are  needed for an ODBC Agent.

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = <odbc data_source_name>

HS_FDS_TRACE_LEVEL = <trace_level>

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

Change it to ……………….. --

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

needed for an ODBC Agent.

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = MYSQLSERVER

HS_FDS_TRACE_LEVEL  = OFF

 #

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

Open  listener.ora in WordPad … Its look like:

# This is a sample listener.ora that contains the NET8 

parameters that are

# needed to connect to an HS Agent

# LISTENER =

#  (ADDRESS_LIST=

#       (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))

#       (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

# SID_LIST_LISTENER=

#   (SID_LIST=

#       (SID_DESC=

#          (SID_NAME=hsagent)

#          (ORACLE_HOME=/private/app/oracle/product/9.0.1)

#          (PROGRAM=hsagent)

#        )

#       )

#CONNECT_TIMEOUT_LISTENER = 0

Change it to ……………….. --

1. Created my own listener name of LISTENER_MYSQLSERVER
2. Changed the Port number to 1522
3. Changed the SID_NAME to my DSN (MYSQLSERVER)
4. Changed the ORACLE_HOME location
5. Changed the PROGRAM to hsodbc

 

LISTENER_MYSQLSERVER =

 (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER_MYSQLSERVER=

  (SID_LIST=

      (SID_DESC=

         (SID_NAME=MYSQLSERVER)

         (ORACLE_HOME = c:\oracle\product\10.1.0\db_1)

         (PROGRAM=hsodbc)

       )

      )

 

Open  tnsnames.ora in WordPad … Its look like:

# This is a sample tnsnames.ora that contains the NET8 parameters that are

# needed to connect to an HS Agent

#hsagent  =

#  (DESCRIPTION=

#    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))

#    (CONNECT_DATA=(SID=hsagent))

#    (HS=)

#  )

Change it to ………………..--

1.      Create TNS entry named MYSQLSERVER

2.      Changed the Port number to 1522

3.      Changed the SID to my DSN (MYSQLSERVER)

4.      Added OK to the HS= parameter

MYSQLSERVER  =

  (DESCRIPTION=

    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))

    (CONNECT_DATA=(SID=MYSQLSERVER))

    (HS=OK)

  )

Step 3: Start & Test new Listener

Command to Start Listener

C:\>lsnrctl start LISTENER_MYSQLSERVER

Command to Test Listener is pinging

C:\>tnsping LISTENER_MYSQLSERVER

Step 4: Create a Database Link

On Sqlplus enter following command

SQL> create public database link MY_SQL_SERVER 

     Connect to USER_NAME identified by USER_PASSWORD

using 'MYSQLSERVER';

Database link created.

Note: use credential --

USER_NAME & USER_PASSWORD from Remote SQL Server

Step 5: Connect With Remote SQL Server using DB Link

SQL> select "EmployeeID","LastName","FirstName" from employees@MY_SQL_SERVER;