Connect to SQL Server 2000 (Instance) from Oracle 9i through database link)

Connecting to SQL Server2000 from Oracle 9i requires a transparent gateway through which we can manipulate other databases such as MS sqlserver, Sybase, infomix, etc, implement heterogeneous database services.

There is no transparent gateway in Oracle 8i and 9i comes with it, but it is not installed by default.

(1) install transparent gateway for Windows SQL Server:

* If the tg4msql folder exists in the Oracle main directory, you do not need to reinstall it.

1. Start setup.exe in disk 5 of the Oracle installation disk.

2. Press next twice, select Oracle 9i Database 9.2.0.1.0, and press next.

3. select Custom and press next.

4. Press "+" next to Oracle transparent gateway 9.2.0.1.0, select Oracle transparent gateway for Microsoft SQL Server 9.2.0.1.0, and press next.

5. Install the SDK.

6. After the installation is complete, the tg4msql folder will appear in the Oracle main directory.

(2) Configuration:

My environment:

Oracle Server:

OS: Windows XP (SP2)

IP: 192.168.1.180

Port: 1521

SQL Server:

OS: Windows 2000

IP: 192.168.1.213

Database Server Name: biobench

Database Name: salesdata1

(A) connect through tg4msql:

1. Copy inittg4msql. ora from the tg4msql \ ADMIN directory in the Oracle main directory and change it to init <Sid>. ora. For example, if my Sid is MSSQL, then my file name is initmssql. ora.

2. Configure init <Sid>. ora as follows:

******************************

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

# Needed for the transparent gateway for SQL Server

#

# HS init Parameters

#

Hs_fds_connect_info = "Server = biotify; database = salesdata1"

Hs_fds_trace_level = off

Hs_fds_recovery_account = recover

Hs_fds_recovery_pwd = recover

******************************

3. Configure listener. ora in the Network \ ADMIN directory of the Oracle main directory. Take my example as follows:

* Newlistener is the name of the new listener. The default value is listener.

******************************

# Listener. ora network configuration file: C: \ oracle \ ora92 \ Network \ admin \ listener. ora

# Generated by Oracle configuration tools.

Newlistener =

(Description_list =

(Description =

(Address_list =

(Address = (Protocol = TCP) (host = 192.168.1.180) (Port = 1521 ))

)

)

)

Sid_list_newlistener =

(Sid_list =

(Sid_desc =

(ORACLE_HOME = c: \ oracle \ ora92)

(Sid_name = MSSQL)

(Program = tg4msql)

)

)

******************************

4. Configure tnsnames. ora in the Network \ ADMIN directory of the Oracle main directory. In my example:

******************************

MSSQL =

(Description =

(Address = (Protocol = TCP) (host = 192.168.1.180) (Port = 1521 ))

(CONNECT_DATA = (SID = MSSQL ))

(HS = MSSQL)

)

******************************

5. Restart listener.

6. Use sqlplus to establish and test the database link. In the command line, run the following command:

Sqlplus/nolog

Conn sys/webpos as sysdba ----> log in as sysdba

Alter system set global_names = true; ----> set global_names = false. Do not set the database link to be consistent with the global name of the target database.

Create database link test1 connect to sa identified by SA using 'mssql ';

Select * From salesdata1 @ test1;

7. The connection is successful. Connect to is followed by a user name, identified by is followed by a password, and using is followed by a Sid.

(B) connection via hsodbc: 

1. Establish the system DSN for SQL Server connection in ODBC. I use the name biodrivers. (The steps are not detailed. Please refer to msdn)

 

2. Copy inithsodbc. ora and change it to init <Sid>. ora under the HS \ ADMIN directory in the Oracle main directory. This time, the Sid I used is bioexample, so the file name is bioexample. Take my example as follows:

* Hs_fds_connect_info is followed by the data source name.

******************************

# 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 = bioworkflow

Hs_fds_trace_level = off

#

# Environment variables required for the non-Oracle System

#

# Set <envvar >=< value>

******************************



3. Configure listener. ora. This time, add the following section:

******************************

(Sid_desc =

(ORACLE_HOME = c: \ oracle \ ora92)

(Sid_name = bioworkflow)

(Program = hsodbc)

)

******************************

Therefore, all my listener. ora files are as follows:

******************************

# Listener. ora network configuration file: C: \ oracle \ ora92 \ Network \ admin \ listener. ora

# Generated by Oracle configuration tools.

Newlistener =

(Description_list =

(Description =

(Address_list =

(Address = (Protocol = TCP) (host = 192.168.1.180) (Port = 1521 ))

)

)

)

Sid_list_newlistener =

(Sid_list =

(Sid_desc =

(ORACLE_HOME = c: \ oracle \ ora92)

(Sid_name = MSSQL)

(Program = tg4msql)

)

(Sid_desc =

(ORACLE_HOME = c: \ oracle \ ora92)

(Sid_name = bioworkflow)

(Program = hsodbc)

)

)

******************************

4. Configure tnsnames. ora as follows:

******************************

Bioworkflow =

(Description =

(Address = (Protocol = TCP) (host = 192.168.1.180) (Port = 1521 ))

(CONNECT_DATA = (SID = biotify ))

(HS = OK)

)

******************************

5. Restart listener and establish a connection.

(3) Note:

When I was testing, there was always a ORA-28545 error at first, the meaning of the investigation is as follows:

ORA-28545 error diagnosed by net8 when connecting to an agent

Cause: an attempt to call an external procedure or to issue SQL to a non-Oracle System on a heterogeneous services database link failed at connection initialization. the error diagnosed by net8 NCR software is reported separately.

Action: refer to the net8 ncro error message. if this isn't clear, check connection administrative setup in tnsnames. ora and listener. ora for the service associated with the heterogeneous services database link being used, or with 'extproc _ connection_data 'for an external procedure call.

I carefully checked the configuration of all the files and found that the listener name caused an error (sid_list_newlistener). Therefore, I suggest you carefully check all the configuration files if this error occurs.

(4) recommended reading:

Managing Oracle heterogeneous services using transparent gateways:

Http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76960/hs_admin.htm

ORA-24289-ORA-29249:

Http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96525/e24280.htm

Making a connection from Oracle to SQL Server (in English ):

Http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1