mssql sqlserver 使用自定义存储过程的方式新建linkserver的方法分享


摘要:
下文讲述使用存储过程新建链接服务器的方法,如下所示:
实验环境:sql server 2008 R2


在sqlserver的开发中,我们经常需要跨库进行数据的导出操作,下文讲述通过自定义存储过程新建链接服务器的方法分享,如下:

create  proc  [dbo].[proc_addLinkserver]  
    @lnkServerIP    nvarchar(60),
    @lnkSQLUserName  nvarchar(60)='',
    @lnkPassW nvarchar(80)='' 
AS
BEGIN

SET NOCOUNT ON;

declare @tmpSQL     nvarchar(4000)
declare @replaceSQL nvarchar(4000)

---检测链接服务器是否存在,如果链接服务器存在,则删除此链接服务器
if  exists(select srvname   from master.dbo.sysservers where srvname <> SERVERPROPERTY('servername') and srvname=@lnkServerIP)
begin
      set @replaceSQL =' EXEC master.dbo.sp_dropserver @server=N'''+@ServerIP+''', @droplogins=''droplogins''' 
     exec sp_executesql @replaceSQL 
end

  --生成建立链接服务器的模版sql
  set @tmpSQL ='/****** Object:  LinkedServer [] ******/
              EXEC master.dbo.sp_addlinkedserver @server = N'''', @srvproduct=N''SQL Server''
               /* For security reasons the linked server remote logins password is changed with ######## */
                EXEC master.dbo.sp_addlinkedsrvlogin    @rmtsrvname=N'''',@useself=N''False'',@locallogin=NULL,@rmtuser=N'''',@rmtpassword=''''
                --GO
                EXEC master.dbo.sp_serveroption @server=N'''', @optname=N''collation compatible'', @optvalue=N''true''
                --GO
                EXEC master.dbo.sp_serveroption @server=N'''', @optname=N''data access'', @optvalue=N''true''
                --Go
               EXEC master.dbo.sp_serveroption @server=N'''', @optname=N''rpc'', @optvalue=N''true''
                --GO
                EXEC master.dbo.sp_serveroption @server=N'''', @optname=N''rpc out'', @optvalue=N''true'
                --GO
                EXEC master.dbo.sp_serveroption @server=N'''', @optname=N''use remote collation'', @optvalue=N''true''
                --GO
                EXEC master.dbo.sp_serveroption @server=N'''', @optname=N''remote proc transaction promotion'', @optvalue=N''true''
                --GO'

        

        set  @replaceSQL=REPLACE( @tmpSQL, '',@lnkServerIP )
        set  @replaceSQL=REPLACE(@replaceSQL, '',@lnkSQLUserName )
        set  @replaceSQL=REPLACE(@replaceSQL, '',@lnkPassW )

        begin try
             exec sp_executesql @replaceSQL 
             print @replaceSQL 
        end try

        begin catch
            print '创建链接服务器失败 '      
        end catch
END
GO

--测试脚本,创建链接服务器
exec proc_addLinkserver '链接服务器ip','链接服务器sql账户','链接服务器sql密码'