通过存储过程（SP）实现SQL Server链接服务器（LinkServer）的添加

1. 背景

当系统的微服务化做的不是很高的时候，部分功能要通过db linkserver 来实现跨 server 查询，当然，有时候bi抽数据、dba数据库维护可能也会创建linkserver。

特别是当 db迁移的时候，我们需要检查、创建 db linkserver。

 

2.脚本实现

下面是我们创建的一个存储过程，通过这个存储过程来实现简单、快速的添加链接服务器。

 

use [dba_manager]
go

/****** object:  storedprocedure [dbo].[usp_create_dblink]    script date: 2019/7/5 13:52:50 ******/
set ansi_nulls on
go

set quoted_identifier on
go


-- =============================================
-- author:        <author,,carson>
-- create date: <create date,2018-06-18,>
-- description:    <description,实现创建db linkserver的脚本化,>
-- =============================================
create procedure [dbo].[usp_create_dblink] 
    -- add the parameters for the stored procedure here
    @serverip varchar(20),@sqlusername varchar(20)='',@passw varchar(20)='',@delcurlinks varchar(10)='n', @result nvarchar(3000)='' output
as
begin

set nocount on;

declare @originalsql nvarchar(3000)
declare @ssql nvarchar(3000)
 
----------------------------------------------

---判断指定的serverip是否已存在dblinkserver，结合@delcurlinks输入参数判断是否删除重建

if @delcurlinks='y' and exists(select srvname   from master.dbo.sysservers where srvname <> serverproperty('servername') and srvname=@serverip)
begin
    set @ssql=' exec master.dbo.sp_dropserver @server=n'''+@serverip+''', @droplogins=''droplogins''' 
    print @ssql    
    exec sp_executesql @ssql 
end

if not exists(select srvname   from master.dbo.sysservers where srvname <> serverproperty('servername') and srvname=@serverip) or @delcurlinks='y'
begin
        --create  script 
        set @originalsql='/****** object:  linkedserver [<serverip>] ******/
                exec master.dbo.sp_addlinkedserver @server = n''<serverip>'', @srvproduct=n''sql server''
                 /* for security reasons the linked server remote logins password is changed with ######## */
                exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname=n''<serverip>'',@useself=n''false'',@locallogin=null,@rmtuser=n''<username>'',@rmtpassword=''<pwd>''
                --go
                exec master.dbo.sp_serveroption @server=n''<serverip>'', @optname=n''collation compatible'', @optvalue=n''true''
                --go
                exec master.dbo.sp_serveroption @server=n''<serverip>'', @optname=n''data access'', @optvalue=n''true''
                --go
                exec master.dbo.sp_serveroption @server=n''<serverip>'', @optname=n''rpc'', @optvalue=n''true''
                --go
                exec master.dbo.sp_serveroption @server=n''<serverip>'', @optname=n''rpc out'', @optvalue=n''true''
                --go
                exec master.dbo.sp_serveroption @server=n''<serverip>'', @optname=n''use remote collation'', @optvalue=n''true''
                --go
                exec master.dbo.sp_serveroption @server=n''<serverip>'', @optname=n''remote proc transaction promotion'', @optvalue=n''true''
                --go
                '
        
        set  @ssql=replace( @originalsql, '<serverip>',@serverip )
        set  @ssql=replace( @ssql, '<username>',@sqlusername )
        set  @ssql=replace( @ssql, '<pwd>',@passw )

        begin try
             exec sp_executesql @ssql 
             print @ssql
             print 'create link server['+ @serverip +'] successfully!'
        end try

        begin catch
            print 'create link server ['+ @serverip +'] fail! errmsg: '+error_message()     
        end catch

end 

if @delcurlinks='n' and exists(select srvname   from master.dbo.sysservers where srvname <> serverproperty('servername') and srvname=@serverip)
begin
    print 'create link server ['+ @serverip +'] fail! errmsg: find dblinkserver of the same name ,please check it.'
end

end


go

 

3.方法使用

方法 1: 只输入ip、uid、pwd三个参数，@delcurlinks不显示输入【此时，@delcurlinks默认为 n，指明当存在相同的linkserver时，不删除直接退出。】

exec usp_create_dblink '172.xxx.xxx.xxx','uid','pwd'

方法 2：输入ip、uid、pwd、delcurlinks 四个参数，显示指明当存在相同的linkserver时，不删除直接退出。

exec usp_create_dblink '172.xxx.xxx.xxx','uid','pwd','n'

方法 3：输入ip、uid、pwd、delcurlinks 四个参数，显示指明当存在相同的linkserver时，删除重新创建

exec usp_create_dblink '172.xxx.xxx.xxx','uid','pwd','y'

 

4. 其它知识

 （1） 查询本sql server 已创建的所有实例

select srvname as '链接服务器'   from master.dbo.sysservers where srvname <> serverproperty('servername')

（2）删除已建立的链接服务器（linkserver）

exec master.dbo.sp_dropserver @server=n'172.xxx.xxx.xxx', @droplogins='droplogins'