SQL Server to Oracle Connection server implementation

Implementation of oracle|server| Server to Oracle Connection server
Author: Jennifer
This article takes SQL Server 2k as an example to illustrate the specific implementation of SQL Server to an Oracle connection server.

1. require PC to install Oralce client software and sqlserver2000 software.

2. Client configuration Tnsnames.ora files, configuration of the database server to be connected (Windows,unix and other platforms can)
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 Windows ODBC Data source:
Start Menu-"Settings-" admin tool-"data source (ODBC)-" Enter configuration User DSN or System DSN can: Add--Select Microsoft ODBC for oracle-

Custom data source name (preferably with the same name as the connection string in TNS!) )-"Server name" (required) Fill in the name of the connection string in the TNS file)-"complete."

4. Configure the connection server in sqlserver2000:
Enterprise Manager-"Security-" Connection server-"right" New connection server-"define the connection name; select another data source; Specify the program name: Microsoft OLE DB

Provider for Oracle; Product name is not filled; Data source Specifies the name of the data source that is defined in ODBC just now; the provider string is filled in the following format: User

Id=username; PASSWORD=USERPASSWD (or in the following format: Uid=username; PWD=USERPASSWD), where the username and password correspond to the Oracle database to which you want to connect

Username and password-security tab: Set to use this security context and enter Oracle's database username and password-the Server Option tab page can be default-"

Set

5. All the preparations are complete! In SQL Server Enterprise Manager-"Security-" Connect server open just built connection server-"Click on the table, you can see the ORAC in the right window

Le database users have all the table names, but here is not able to view the table records, this need in the Sqserver Query Analyzer with a specific SQL implementation! When accessing a table, use the format

To: [Connect server name]. [Oracle users]. [Table name]. More detailed and specific use here no longer repeat.