Step 1:
Create a DSN (eg. MSSQL) entry in “/etc/odbc.ini” file by providing the below details
Description = "Description of the connection user identification"
Driver = "Driver path for SQL server"
Server = "SQL Server database server name>"
Port = 5432 "this is default port number"
User = "username for connecting the sql server database"
Password = "password for connect the sql server database"
Sid = "SQL server database name"
Trace = No "default value"
TraceFile = null "default value"
Database = "Database name"
Servername = "Server name were the SQL server database exists"
Username = "database username"
Step 2:
Copy inithsodbc.ora file in ORACLE_HOME \hs\admin folder as initMSSQL.ora file and modify entries like
HS_FDS_CONNECT_INFO = MSSQL --> DSN Name which we created using ODBC
HS_FDS_TRACE_LEVEL = OFF
Step 3:
Add one more entry to SID_List_Listener in Listener.ora file in ORACLE_HOME\Network\Admin folder like the below
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = MSSQL) à DSN Name used as SID of SQL server
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = hsodbc)
)
)
Step 4:
Now create a TNS entry as the following in TnsNames.ora file in Oracle_Home\Network\Admin
MSSQL =
(DESCRIPTION=
(CONNECT_DATA=(SID=MSSQL))
(ADDRESS = (PROTOCOL = TCP)(HOST = C202878)(PORT = 1521))
(HS=OK)
)
Step 5:
Reload the listener.
Step 6:
Try tnspinging the sql server SID and check for the response.
U:\>tnsping MSSQL
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-SEP-2009 14:56:26
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SID= MSSQL)) (ADDRESS = (PROTOCOL = TCP)(HOST = local host)(PORT = 1521)) (HS=OK))
OK (30 msec)
Step 7:
Create DB link using the above SID and try connecting.
By Using Oracle Transparent Gateways for Microsoft SQL server:
Step 1:
Install Transparent Gateway for Microsoft SQL server.
While installing it will ask for the server name and MS SQL server database name .Give the Server name where the SQL server database exists and the database name which you are planning to connect.
Installing this component will create a folder “tg4msql” in under ORACLE_HOME location.
Inside this we will have some configuration files inside “D:\oracle\product\10.2.0\db_1\tg4msql\admin” folder.
Optional (only needed when configuring for multiple SQL servers)
Take a copy of inittg4msql.ora file in the ORACLE_HOME\tg4msql\admin folder and change the name of file as init<
Step 2:
Add one more entry to SID_List_Listener in Listener.ora file in ORACLE_HOME\Network\Admin folder like the below
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = tg4msql) à SID for SQL Server connectivity in Listener.ora
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql) à Identifies transparent gateway for MS SQL Server
)
)
Step 3:
Now create a TNS entry as the following in TnsNames.ora file in Oracle_Home\Network\Admin
tg4msql =
(DESCRIPTION=
(CONNECT_DATA=(SID= tg4msql )) à SID of the MSQL server database.
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
(HS=OK) à Need to identify the connection as a heterogeneous connection
)
Step 4:
Reload the listener.
Step 5:
Try tnspinging the sql server SID and check for the response.
U:\>tnsping tg4msql
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-SEP-2009 14:56:26
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SID= tg4msql)) (ADDRESS = (PROTOCOL = TCP)(HOST = local host)(PORT = 1521)) (HS=OK))
OK (30 msec)
Step 6:
Create DB link using the above SID and try connecting.
