Tuesday, July 28, 2015

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; 
 
 

No comments:

Post a Comment