How to use database links under SQL Server

Sometimes we want to access data on another SQL Server database under one SQL Server, or access data on other Oracle databases, and to do so, we first create a database link.

A database link enables a local SQL Server logged-on user to map to a remote database server, as well as to operate a local database. So how do you create a database link? I now have two ways to achieve this.

First type: In SQL Server Enterprise Manager, set up, this relatively simple, first in the security--〉 database links on the right, in the menu that appears to create a new database link, and then pop an interface, we need to fill out a: Linked server (This is a name, their own according to the circumstances of their own definition, In the future will be through his remote access to the provider name (this is the selection of data driven, according to the type of database to choose, can not be randomly selected, otherwise linked), data sources (for SQL Server is the remote database server hostname or IP, for Oracle is in Oracle NET config, the security context user and password (that is, the user and password of the remote server).

Second: using System stored procedures

Create a database link for SQL Server to SQL Server:

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 '

To create a database link for SQL Server to Oracle:

exec sp_addlinkedserver ' Link_ora ', ' Oracle ', ' MSDAORA ', ' Oracle database server alias '
exec sp_addlinkedsrvlogin ' Link_ora ', false, ' sa ', ' username ', ' user password '

With a database link we can use it. There is a difference between the methods used in SQL Server and Oracle.

For SQL Server:

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 for Table name

For Oracle:

CREATE VIEW VVV as SELECT * from Link_ora ... Northsnow. Sys_user
SELECT * from VVV;
Where Northsnow is a user name for a remote Oracle database server, Sys_user is a table for that user on that server, and it is important to note that there are two points behind the database link (link_ora) (...). , and then must be all uppercase, the object of the query is generally a table or view, can not query synonyms.

There are two ways to delete a database link,

One is to operate in Enterprise Manager, this is simple.

The other is using a system stored procedure:

EXEC sp_dropserver Database link name, ' droplogins '

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