How to use database links in SQL Server

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, in the sqlserver Enterprise Manager, click "security"> "Database Link" and click "create database link" in the displayed menu, then, a page will pop up, and we need to fill in the Link server (this is a name defined by ourselves based on the situation, which will be used for remote access in the future) to provideProgramName (this is the data driver selected based on the database type and cannot be randomly selected, or cannot be linked), data source (for sqlserver, it is the host name or IP address of the remote database server, 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'

Finally finished writing, interested please leave a message or email me: northsnow@163.com