Quick understanding of the use of database links under SQLServer (2) _ MySQL

Quick understanding of the use of database links under SQLServer (2) bitsCN.com

Right-click on the page and click "create database link" in the displayed menu. 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, 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 ', '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 way is to operate in the enterprise manager.

The other is to use the system stored procedure:

Exec sp_dropserver

Database link name, 'droplogins '.

BitsCN.com