How to connect SQL Server to other database servers

In practice, two databases (not on the same host) are often associated. This mechanism is provided in Oracle to connect Oracle databases on other hosts.
Similarly, in SQL server2005, you can also link to other SQL Server databases, and use OLE DB to link to Oracle, Sybase, and other different databases to facilitate user operations and maintenance.

The Microsoft SQL Server Management studio tool provides graphic operations:

However, it is troublesome to fill in some of the parameters. Here I will link SQL Server and Oracle for example.
(The command method only lists the simplest commands. I have removed some settings of dispensable attributes)

1. Link to the SQL Server database
This is relatively simple. See the following SQL statement use [Master]
Go
Exec master. DBO. sp_addmediaserver @ Server = n 'dbserver', @ srvproduct = n 'SQL Server'
Go
Use [Master]
Go
Exec master. DBO. sp_addmediasrvlogin @ rmtsrvname = n'dbserver', @ locallogin = n'sa ', @ useself = n'false', @ rmtuser = n'sa', @ rmtpassword = n'1'
Go

Two stored procedures are called:
Sp_addmediaserver is a link to the database. The server parameter is the name of another SQL Server server.
Sp_addlinkedsrvlogin is the username that is added to the corresponding remote database, and rmtuser and rmtpassword are the username and password of the remote database.

The following code queries the table of the remote database locally: Select * from [dbserver]. [helpdesk]. [DBO]. [messages];

2. Connect to the Oracle database
To connect to the Oracle database, first install the Oracle client locally and configure the Oracle alias (I will not talk about alias configuration here, it is very simple)
Then, the preceding two stored procedures are called: use [Master].
Go
Exec master. DBO. sp_addmediaserver @ Server = n 'gspring', @ srvproduct = n 'oracle ', @ provider = n 'msdaora', @ datasrc = n 'gspring'
Go
Use [Master]
Go
Exec master. DBO. sp_addmediasrvlogin @ rmtsrvname = n'gspring', @ locallogin = n'sa ', @ useself = n'false', @ rmtuser = n'user1', @ rmtpassword = n'1'
Go

The server is the name of the connected database, and the datasrc is the alias of the local Oracle configuration.

The following code queries the table of the remote ORACLE database locally: Select * from [gspring]... [user1]. [Table1];

User1 is the owner of table 1.
Note that the full path for accessing a table in sqlserver is four layers, so when accessing Oracle, it should be replaced by an empty one.

Others:
1. All links configured locally can be found in the sysservers table: Select * From SYS. sysservers;

2. When accessing a remote database table, you can also create a synonym locally, and then access the table as locally: use [Master]
Go
Create synonym [DBO]. [linktable] for [dbserver]. [helpdesk]. [DBO]. [messages];
Go

Select * From linktable;

3. If other errors occur when configuring Oracle, see Microsoft's solution (I didn't see a problem during the configuration process, is it too good for my RP): http://support.microsoft.com/kb/280106/zh-cn