MS SqL Server跨服务器查询

----Add server link

 

EXEC sp_addlinkedserver

@server = N'telsqlserverdb1', ---servername

@srvproduct = N'MS SQL',

@datasrc = N'10.2.1.11' , -----IP

@provider = N'SQLNCLI';

 

----Add server login

 EXEC sp_addlinkedsrvlogin

@rmtsrvname = N'telsqlserverdb1' , ---servername

@useself = N'false' ,

@locallogin = NULL ,

@rmtuser = N'usename' ,---UserName

@rmtpassword = N'******';--Password

 

 

---Delete server link

EXEC sp_dropserver 'telsqlserverdb1','droplogins' ;

 

 

 

示例：

---add

EXEC sp_addlinkedserver

@server = N'telsqlserverdb1', ---servername

@srvproduct = N'MS SQL',

@datasrc = N'10.2.1.11' , -----IP

@provider = N'SQLNCLI';

----Add server login

 EXEC sp_addlinkedsrvlogin

@rmtsrvname = N'telsqlserverdb1' , ---servername

@useself = N'false' ,

@locallogin = NULL ,

@rmtuser = N'usename' ,---UserName

@rmtpassword = N'******';--Password
 

--query
 SELECT * FROM telsqlserverdb1.database.schema.table;

 

 

---Delete server link

EXEC sp_dropserver 'telsqlserverdb1','droplogins' ;

 

 

参考链接：

http://hi.baidu.com/chssheng2007/blog/item/41ef20ef4ef0c41dfcfa3c44.html

http://topic.csdn.net/t/20030902/10/2214024.html

http://blog.csdn.net/luobailian/archive/2010/06/09/5658172.aspx