SQL Server Add linked server

Original: SQL Server add linked server

Background

In SQL Server, if we query the database to associate a table in another database, in this case we can do so by adding a server link.

Case List

Mode 1. SQL Server provides a graphical interface, as follows:

Right-click >>new Linked Server

Fill in the relevant parameters.

mode 2. SQL Server statements are added as follows:

--code to release the bitter monkEXECMaster.dbo.sp_addlinkedserver@server =N'192.168.1.22',@srvproduct=N'SQL Server' EXECMaster.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'192.168.1.22',@useself=N'False',@locallogin=NULL,@rmtuser=N'SA',@rmtpassword='abcd1234'

At query time, the statement is:

Select *  from [192.168.1.22]. TestDB.dbo.Table_1

Comments:

[@useself =] ' Useself '

Determines the login name used to connect to the remote server. The useself data type is varchar (8) and is set to TRUE by default. A value of true specifies that SQL Server verifies that the login uses its own credentials to connect to the rmtsrvname, while ignoring the rmtuser and Rmtpassword parameters. A value of false specifies the rmtsrvname that the rmtuser and Rmtpassword parameters use to connect to a specific locallogin. If Rmtuser and Rmtpassword are also set to NULL, no login or password is required to connect to the linked server. For Windows NT authentication logins, useself is not valid unless the Microsoft Windows NT® Environment supports security account delegation, and the provider supports Windows authentication (at this time no longer need to create a mapping with a value of true, not is still valid).

[@locallogin =] ' LocalLogin '

Login on the local server. The LocalLogin data type is sysname and is set to NULL by default. A NULL value specifies that this entry will be applied to all local logins connected to rmtsrvname. If the value is not NULL, then locallogin can be a SQL Server login or a Windows NT user. Windows NT users must be granted access to SQL Server directly or through their members of the group to which they have been granted access.
Data operations between different server databases

Link Oracle

 Use [Master]GOEXECMaster.dbo.sp_addlinkedserver@server =N'gspring',@srvproduct=N'Oracle',@provider=N'Msdaora',@datasrc=N'gspring'GO Use [Master]GOEXECMaster.dbo.sp_addlinkedsrvlogin@rmtsrvname =N'gspring',@locallogin =N'SA',@useself =N'False',@rmtuser =N'User1',@rmtpassword =N'1'GO

Other:

1, the local configuration of all the links can be found in the sysservers table:

Select *  from Sys.sysservers;

2. When accessing a remote database table, you can also create a synonym locally, and then you can access it as if it were local:

 Use [Master]GOCREATESynonym[dbo].[linktable]  for [DBServer].[Helpdesk].[dbo].[Messages];GOSelect *  fromlinktable;

3. Delete Linkserver

'10.58.1.69 ','droplogins     

Reference: Http://zhidao.baidu.com/link?url=EvgnFGejOO8h61W-WHNKgyyI3w4rp-B9SGBEOG1Ve_zhXL_ Lnbpjvm124-6pjm87x4ejwzhc0iowx13po9vzi5jgu5c7c7nxe5ylpboek8q

Http://www.cnblogs.com/firstyi/archive/2007/08/09/849494.html

The source of the Buddhist monk: http://www.cnblogs.com/woxpp/p/3990375.html This copyright belongs to the author and the blog Park is shared, welcome reprint, but without the author's consent must retain this paragraph, and in the article page obvious location to the original link.

SQL Server Add linked server