How to use database links under SqlServer

Sometimes we want to access data from another sqlserver database under one sqlserver, or access data from other oracle databases. to complete these operations, we must first create a database link.

The database link allows a local sqlserver login user to map to a remote database server, just like operating a local database. So how to create a database link? I have two methods to achieve this.

First, right-click the database link node under the "Server Object" node, click "create database link" in the displayed menu, and an interface will pop up. You need to enter the link server (this is a name and can be customized as needed, in the future, you will need to remotely access the Program), provide the program name (this is to select the data Driver Based on the database type, and cannot be randomly selected, otherwise the link will not be available ), data Source (sqlserver is the host name or IP address of the remote database server, and oracle is the alias configured in oracle net config ), security Context user and password (that is, the remote server user and password ).

Second: using system stored procedures

Create a sqlserver database link for sqlserver:

Exec sp_addmediaserver 'link _ northsnow', '', 'sqlodb', 'remote server host name or domain name or IP address'
Exec sp_add1_srvlogin 'link _ northsnow', 'false', null, 'username', 'user password'

Create a sqlserver database link for Oracle:

Exec sp_addmediaserver 'link _ ora ', 'oracle', 'msdaora ', and 'oracle Database Server alias'
Exec sp_add1_srvlogin 'link _ ora ', false, 'sa', 'username', 'user password'

With the database link, we can use it. There are differences between SQL Server and oracle.

For sqlserver:

Create view v_lhsy_user as select * from link_northsnow.lhsy.dbo.sys_user
Select * from v_lhsy_user
Lhsy indicates the remote database name.
Sys_user is the table name.

For oracle:

Create view vvv as select * from link_ora... NORTHSNOW. SYS_USER
Select * from vvv;
Among them, northsnow is a username of the remote oracle database server, and SYS_USER is a table of the user on the server. Note that the database link (link_ora) there are two dots (...) behind (..), the query object is generally a table or view and cannot be used to query synonyms.

There are two ways to delete a database link,

One is to operate in the Enterprise Manager, which is simple.

The other is to use the system stored procedure:

Exec sp_dropserver Database Link name, 'droplogins'