SQL建立DB LINK的方法

--login server
EXEC master.dbo.sp_addlinkedserver @server = N'ServerName2', @provider = N'SQLOLEDB', @datasrc = N'10.2.64.58', @srvproduct=''
--login server db
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerName2',@useself=N'False',@locallogin=NULL,@rmtuser=N'eiso',@rmtpassword='eiso#pswd'

--select * from ServerName2.eiso.dbo.isoapply

--Remove DB Link Server
Exec sp_droplinkedsrvlogin 'ServerName2',Null
Exec sp_dropserver 'ServerName2'

 

 

EXEC sp_addlinkedserver
@server = 'F6', --Server Name
@srvproduct = 'MS SQL',
@datasrc = '192.168.1.1' , --Server IP
@provider = 'SQLNCLI'


--Remove DB Link Server
EXEC sp_dropserver
@server = 'F6' --Server Name


--Add Login User and Password
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'F6' , --Server Name
@useself = 'false' ,
@locallogin = NULL ,
@rmtuser = 'sa' , --User
@rmtpassword = 'sasa' --Password

view plaincopy to clipboardprint?
--Query DB Link Server
select * from sys.servers
--Query DB Link Server
select * from sys.servers



view plaincopy to clipboardprint?
--Query Login User
select * from sys.linked_logins
--Query Login User
select * from sys.linked_logins



view plaincopy to clipboardprint?
--Query DB Link Data
select * from [F6].[database].[dbo].[table]

--select * from [server name].[database name].[owner name].[table name]
--Query DB Link Data
select * from [F6].[database].[dbo].[table]

--select * from [server name].[database name].[owner name].[table name]

 

參考網址:

http://space.itpub.net/12184684/viewspace-504542
http://www.cnblogs.com/aierong/archive/2005/03/16/119696.html
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx
http://tsuozoe.pixnet.net/blog/post/22417837
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/connecting-to-different-sql-server-t-sql-52929