Implementation of connecting SQL Server to Oracle Server

This document uses SQLServer2k as an example to illustrate the specific implementation process of connecting SQLServer to the Oracle server. 1. Install the oralce client software and sqlserver2000 software on the PC.

This article uses SQL Server 2 K as an example to illustrate the specific implementation process of connecting SQL Server to the Oracle Server. 1. Install the oralce client software and sqlserver2000 software on the PC.

This article uses SQL Server 2 K as an example to illustrate the specific implementation process of connecting SQL Server to the Oracle Server.

1. Install the oralce client software and sqlserver2000 software on the PC.

2. Configure the tnsnames. ora file on the client and configure the database server to be connected (for windows, unix, and other platforms)

Eg: tnsnames. ora

......

TEST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.1.11.90) (PORT = 1521 ))

)

(CONNECT_DATA =

(Sid = blue)

(SERVER = DEDICATED)

)

)

......

3. Configure the ODBC data source for windows:

Choose "start"> "Settings"> "Administrative Tools"> "Data Source (ODBC)"> "Configure user DSN" or "system DSN: add-select Microsoft ODBC for oracle-Custom Data Source Name (preferably the same name as the connection string in tns !) -Server name (required! Enter the connection string name in the tns file.

4. Configure the connection server in sqlserver2000:

Enterprise Manager-security-connection server-right-click Create connection server-define the connection name; select another data source; specify the program name as Microsoft ole db Provider for Oracle; the product name is optional. The data source specifies the name of the data source defined in ODBC. The provider string is in the following format: User ID = username; Password = userpasswd (or in the following format: UID = username; PWD = userpasswd). Here, the user name and password correspond to the user name and password in the oracle database to be connected-> Security Tab: set to use this security context, enter the oracle database username and password. The server option tab is displayed by default.

5. All preparations have been completed! In sqlserver Enterprise Manager-> Security-> connection server, open the newly created connection server-> click the table, and you can see all the table names owned by the oracle database user in the right window, however, the table records cannot be viewed here. You need to use specific SQL statements in the SQL Server Query analyzer! When accessing a table, the format is [connection server name]... [ORACLE user]. [Table name]. For more details, I will not go into details here.

,