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 a different 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 username and password in the Oracle database to be connected-security tab: Set to use this security context and enter Oracle's database username and password-the Server Option tab page can be determined by default-".
5. All the preparations are complete! In SQL Server Enterprise Manager-security-Connect server to open a newly built connection server-click on the table to see all the table names that the Oracle database user has in the right window, but you cannot view the table's records here. This needs to be implemented in the Sqserver Query Analyzer with specific SQL! When accessing a table, use the format: [Connect server name]. [Oracle users]. [Table name]. More detailed and specific use here no longer repeat.
