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 ……………….. --
- Created my own listener name of LISTENER_MYSQLSERVER
- Changed the Port number to 1522
- Changed the SID_NAME to my DSN (MYSQLSERVER)
- Changed the ORACLE_HOME location
- 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;
