sqlserver 多库查询 sp_addlinkedserver使用方法

Exec sp_droplinkedsrvlogin ZYB,Null –删除映射 Exec sp_dropserver ZYB
–删除远程服务器链接 EXEC sp_addlinkedserver
@server=’ZYB’,–被访问的服务器别名 @srvproduct=”, @provider=’SQLOLEDB’,
@datasrc=”/Server2″ –要访问的服务器 EXEC sp_addlinkedsrvlogin ‘ZYB’,
–被访问的服务器别名 ‘false’, NULL, ‘sa’, –帐号 ‘sa’ –密码 使用实例:
Select * from ZYB.CDCenter20110822.dbo.cardbase

sqlserver 多库查询 sp_addlinkedserver使用方法(添加链接服务器)

我们日常使用SQL
Server数据库时,经常遇到需要在实例Instance01中跨实例访问Instance02中的数据。例如在做数据迁移时,如下语句:

insert into Instance01.DB01.dbo.Table01

          select * from Instance02.DB01.dbo.Table01

普通情况下,这样做是不允许的,因为SQL
Server默认不可以跨实例访问数据。解决方案是使用存储过程sp_addlinkedserver进行实例注册。

sp_addlinkedserver在MSDN中的定义为:

sp_addlinkedserver [ @server= ] ‘server’ [ , [ @srvproduct= ]
‘product_name’ ]     

      [ , [ @provider= ] ‘provider_name’ ]    

      [ , [ @datasrc= ] ‘data_source’ ]

      [ , [ @location= ] ‘location’ ]

      [ , [ @provstr= ] ‘provider_string’ ]

      [ , [ @catalog= ] ‘catalog’ ]

例如:在Instance01实例中,执行如下SQL语句EXEC sp_addlinkedserver
‘Instance02’ //只写第一个参数即可,默认情况下,注册的是SQL
Server数据库,其他参数用法详见MSDN。

如果你的两个实例在同一个域中,且Instance01与Instance02有共同的域登陆帐号,那么经过上面的注册后,前面的insert语句就可以执行了。否则,还需要对注册的远程实例进行登陆帐号注册,在Instance01实例中,执行如下SQL语句

EXEC sp_addlinkedsrvlogin ‘InstanceName’,’true’ 
//使用集成认证访问远程实例

或者 EXEC sp_addlinkedsrvlogin
‘InstanceName’,’false’,’TJVictor,’sa’,’Password1′
//使用Windows认证访问远程实例,当用户以TJVictor用户登陆Instance01实例访问Instance02时,默认把TJVictor映射成sa,且密码为Password1

经过
sp_addlinkedserver实例注册和sp_addlinkedsrvlogin登陆帐户注册后,就可以在Instance01中直接访问Instance02中的数据库数据了。

如果还无法访问,请检查本机DNS是否可以解析远程数据库的实例名。如果无法解析,可以在EXEC
sp_addlinkedserver
‘Instance02’中把Instance02换为IP,或者在hosts文件中,自己建立相应DNS映射。

下面列举几个跨实例数据库访问的存储过程和视图。

存储过程名/视图名 作用 举例 
sp_addlinkedserver 注册远程数据库实例 exec sp_addlinkedserver
‘InstanceName’ 
sp_dropserver 删除远程数据库实例 exec sp_dropserver ‘InstanceName’ 
sp_addlinkedsrvlogin 注册远程实例登陆访问帐户 exec
sp_addlinkedsrvlogin ‘InstanceName’, null 
sp_droplinkedsrvlogin 删除远程实例登陆访问帐户 EXEC
sp_droplinkedsrvlogin ‘InstanceName’,’UserName’ 
sp_helpserver
当前实例已注册的可访问的实例(即查看使用sp_addlinkedserver已注册过的实例)
sp_helpserver 
sys.sysservers 功能同sp_helpserver select * from sys.sysservers 
sys.linked_logins
查看已注册的登陆访问帐户(即查看使用sp_addlinkedsrvlogin已注册过的帐户)
select * from sys.linked_logins 
sys.remote_logins 查看已注册的远端访问帐户 select * from
sys.remote_logins

 

Exec sp_droplinkedsrvlogin ZYB,Null
–删除映射(录与链接服务器上远程登录之间的映射) 
Exec sp_dropserver ZYB –删除远程服务器链接 

EXEC sp_addlinkedserver 
@server=’ZYB’,–被访问的服务器别名 
@srvproduct=”, 
@provider=’SQLOLEDB’, 
@datasrc=”/Server2″ –要访问的服务器 

EXEC sp_addlinkedsrvlogin 
‘ZYB’, –被访问的服务器别名 
‘false’, 
NULL, 
‘sa’, –帐号 
‘sa’ –密码 

使用实例: 
Select * from ZYB.CDCenter20110822.dbo.cardbase

 

摘自:

–查看当前链接情况:

select * from sys.servers;

–使用 sp_helpserver
来显示可用的服务器

Exec sp_helpserver

–删除已经存在的某个链接

Exec sp_droplinkedsrvlogin
服务器别名,Null Exec sp_dropserver 服务器别名

–使用sp_addlinkedserver来增加链接

EXEC
sp_addlinkedserver @server=’192.168.2.66′,–被访问的服务器别名(习惯上直接使用目标服务器IP,或取个别名如:JOY)
@srvproduct=”, @provider=’SQLOLEDB’,
@datasrc=’192.168.2.66′ –要访问的服务器

–使用sp_addlinkedsrvlogin
来增加用户登录链接