Shulou(Shulou.com)05/31 Report--

This article shows you how to use database links in Sql Server. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The first kind: in sqlserver Enterprise Manager, establish, this is relatively simple, first right-click on the security-> database link, click New Database Link in the menu that appears, and then there will be an interface that needs us to fill in: link server (this is a name, defined by yourself according to the situation, and will be accessed remotely through him later) Provider name (this is the choice of data driver, selected according to the database type, cannot be randomly selected, otherwise the link cannot be linked), the data source (for sqlserver is the hostname or IP of the remote database server, and for oracle is the alias configured in oracle net config), the security context user and password (that is, the user and password of the remote server).

The second kind: using system stored procedures

Create a database link from sqlserver to sqlserver:

Exec sp_addlinkedserver 'link_northsnow','','SQLOLEDB',' remote server hostname or domain name or ip address'

Exec sp_addlinkedsrvlogin 'link_northsnow','false',null,' username', 'user password'

Create a database link from sqlserver to Oracle:

Exec sp_addlinkedserver 'link_ora',' Oracle', 'MSDAORA',' oracle database server alias'

Exec sp_addlinkedsrvlogin 'link_ora', false,' sa', 'username', 'user password'

We can use it with the database link. There is a difference between the methods used in sqlserver and oracle.

For sqlserver:

Create view v_lhsy_user as select * from link_northsnow.lhsy.dbo.sys_user

Select * from v_lhsy_user

Where lhsy is 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

Northsnow is a user name of the remote oracle database server, and SYS_USER is a table of the user on the server. What you should pay close attention to is that there are two points (..) after the database link (link_ora), and then all capitalization must be followed. The object of the query is generally a table or view, and cannot query synonyms.

There are also two ways to delete a database link

One is to operate in the enterprise manager, which is simple.

The other is to use system stored procedures:

Exec sp_dropserver database link name, 'droplogins'

The above is how to use database links in Sql Server. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.