1. Install the transparent gateway of oracle.
Select custom installation FOR Oracle Installation, then install transparent gateway, and then choose to install components for SQL Server.
Note that only MS-SQL Server 2000 is supported for Oracle 9i, not 2005
After installation, note that a directory C: \ oracle \ ora92 \ tg4msql \ is added to the oracle installation directory \
2. Find the inittg4msql. ora file in the C: \ oracle \ ora92 \ tg4msql \ admin directory and modify it as follows:
HS_FDS_CONNECT_INFO = "SERVER = biztalk2006; DATABASE = upzone"
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_RECOVERY_ACCOUNT = RECOVER
HS_FDS_RECOVERY_PWD = RECOVER
Description: You can use Migration WorkBench in the Oracle tool for configuration. (ODBC needs to be configured in the process)
3. Create a listener and modify the listener. ora file in the C: \ oracle \ ora92 \ network \ admin directory,
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C: \ oracle \ ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORA9)
(ORACLE_HOME = C: \ oracle \ ora92)
(SID_NAME = ORA9)
)
(SID_DESC =
(GLOBAL_DBNAME = tg4msql)
(SID_NAME = upzone)
(ORACLE_HOME = C: \ oracle \ ora92)
(PROGRAM = tg4msql)
)
)
4. Modify TNS (tnsnames. ora)
Upzone =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = BizTalk2006) (PORT = 1521 ))
)
(CONNECT_DATA =
(SID = upzone)
)
(HS = OK)
)
5. Restart the service
Oracle Listener
6. Create DBLink,
Create public database link UPZONE. US. ORACLE. COM
Connect to SA identified by upzone-- User Id & Password for MS-SQL
Using 'upzone ';
Select * from upzone@upzone.us.oracle.com;
Ref: General Oracle connection and transparent gateway
Configure a transparent gateway in Oracle to access MS SQL SERVER