SQL Server 游标运用：批量创建、删除链接服务器

一、背景

我们的数据库比较多，它们提供了外网的访问，我现在想对这些数据库进行一些管理，获取这些数据库的一些信息，我们可以通过什么方式实现呢？

在SQL Server2005版本之后有一个叫做链接服务器的新功能，基本的操作可以参考：

SQL Server 2005链接服务器，我们就通过这个链接服务器来获取我们需要的数据，但是我们的服务器比较多，这个批量创建链接服务器和批量删除链接服务器就呼之欲出了。

 

二、设计过程

设计简述：创建如下图的表结构，LinkName保存远程链接的别名，LinkName2是创建链接方式2的一个补充字段，LinkIP代表远程服务器的地址，如果有端口的还需要加上端口。

这里为什么要设计成LinkName与LinkName2并存呢？这是因为我们在T-SQL使用远程链接的时候是通过别名的，我在进行两种方式的切换，只要修改调换下这两个字段的名称，并且去存储过程sp_CreateLink注释方式1的代码，恢复方式2的代码；

（图1：LinkConfig表，链接方式1）

（图2：LinkConfig表，链接方式2）

（图3：链接方式1的属性）

（图4：链接方式2的属性）

详细代码：创建表LinkConfig、批量创建链接服务器存储过程、批量删除链接服务器存储过程。

 

--创建表CREATE TABLE [dbo].[LinkConfig](    [Id] [int] IDENTITY(1,1) NOT NULL,    [LinkName] [nvarchar](50) NULL,    [LinkName2] [nvarchar](50) NULL,    [LinkIP] [nvarchar](50) NULL,    [LinkSa] [nvarchar](50) NULL,    [LinkPassword] [nvarchar](50) NULL,    [State] [int] NULL CONSTRAINT [DF_LinkConfig_State]  DEFAULT ((0)), CONSTRAINT [PK_LinkConfig] PRIMARY KEY CLUSTERED (    [Id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

--创建远程链接sp-- =============================================-- Author:        <Viajar>-- Create date: <2012.01.05>-- Description:    <创建远程链接>-- =============================================CREATE PROCEDURE [dbo].[sp_CreateLink] ASBEGIN    SET NOCOUNT ON;    --创建远程链接    DECLARE @linkname VARCHAR(100)    DECLARE @linkip VARCHAR(100)    DECLARE @linksa VARCHAR(100)    DECLARE @linkpassword VARCHAR(100)    DECLARE @isexists VARCHAR(10)    DECLARE @sql NVARCHAR(4000)    SET @isexists = 'False'    DECLARE @itemCur CURSOR    SET @itemCur = CURSOR FOR         SELECT [LinkName],[LinkIP],[LinkSa],[LinkPassword] FROM dbo.LinkConfig WHERE State =1    OPEN @itemCur    FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword    WHILE @@FETCH_STATUS=0    BEGIN        --正在处理        PRINT @linkname        --判断是否存在        set @sql =             N'IF EXISTS (SELECT * FROM sys.servers WHERE name = '''+ @linkname + ''')            begin set @IsExistsOUT = ''True'' end'        exec sp_executesql @sql,N'@IsExistsOUT varchar(10) OUTPUT',@IsExistsOUT=@isexists OUTPUT        --不存在        IF(@IsExists = 'False')        BEGIN            --创建链接方式            SET @sql = '            EXEC master.dbo.sp_addlinkedserver @server = N'''+@linkname+''', @srvproduct=N''SQL Server'''            EXEC(@sql)            --设置密码            SET @sql = '            EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'''+@linkname+''', @locallogin = NULL , @useself = N''False'', @rmtuser = N'''+@linksa+''', @rmtpassword = N'''+@linkpassword+''''            EXEC(@sql)--            --创建链接方式--            SET @sql = '--            EXEC master.dbo.sp_addlinkedserver @server = N'''+@linkname+''', @srvproduct=N'''+@linkname+''', @provider=N''SQLNCLI'', @datasrc=N'''+@linkip+''''--            EXEC(@sql)----            --设置密码--            SET @sql = '--            EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'''+@linkname+''', @locallogin = NULL , @useself = N''False'', @rmtuser = N'''+@linksa+''', @rmtpassword = N'''+@linkpassword+''''--            EXEC(@sql)----            --设置属性--            SET @sql = '--            EXEC master.dbo.sp_serveroption @server=N'''+@linkname+''', @optname=N''rpc'', @optvalue=N''true''--            ;--            EXEC master.dbo.sp_serveroption @server=N'''+@linkname+''', @optname=N''rpc out'', @optvalue=N''true''--            '--            EXEC(@sql)        END        SET @isexists = 'False'        FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword    END     CLOSE @itemCur    DEALLOCATE @itemCurEND

--删除远程链接sp-- =============================================-- Author:        <Viajar>-- Create date: <2012.01.05>-- Description:    <删除远程链接>-- =============================================ALTER PROCEDURE [dbo].[sp_DropLink] ASBEGIN    SET NOCOUNT ON;    --删除远程链接    DECLARE @linkname VARCHAR(100)    DECLARE @linkip VARCHAR(100)    DECLARE @linksa VARCHAR(100)    DECLARE @linkpassword VARCHAR(100)    DECLARE @isexists VARCHAR(10)    DECLARE @sql NVARCHAR(4000)    SET @isexists = 'False'    DECLARE @itemCur CURSOR    SET @itemCur = CURSOR FOR         SELECT [LinkName],[LinkIP],[LinkSa],[LinkPassword] FROM dbo.LinkConfig WHERE State =1    OPEN @itemCur    FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword    WHILE @@FETCH_STATUS=0    BEGIN        --正在处理        PRINT @linkname        --判断是否存在        set @sql =             N'IF EXISTS (SELECT * FROM sys.servers WHERE name = '''+ @linkname + ''')            begin set @IsExistsOUT = ''True'' end'        exec sp_executesql @sql,N'@IsExistsOUT varchar(10) OUTPUT',@IsExistsOUT=@isexists OUTPUT        --不存在        IF(@IsExists = 'True')        BEGIN            --删除链接            SET @sql = '            IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'''+@linkname+''')            BEGIN            EXEC master.dbo.sp_dropserver @server=N'''+@linkname+''', @droplogins=''droplogins''            END'            EXEC(@sql)        END        SET @isexists = 'False'        FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword    END     CLOSE @itemCur    DEALLOCATE @itemCurEND

 

三、注意事项

1.      进行方式1与方式2的切换，需要如图1、图2的表字段名称进行修改，并且去存储过程sp_CreateLink注释方式1的代码，恢复方式2的代码；

2.      在需要修改表记录之前需要先删除所有链接服务器（执行存储过程sp_DropLink），再创建链接服务器；（执行存储过程sp_CreateLink）；