SQL Server 批量创建、删除链接服务器代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL Server 批量创建、删除链接服务器
作者:Dezai.CN / 发布于2012/1/29/ 881
<div>--创建表
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]</div>
<div>--创建远程链接sp
-- =============================================
-- Author: <Viajar>
-- Create date: <2012.01.05>
-- Description: <创建远程链接>
-- =============================================
CREATE PROCEDURE [dbo].[sp_CreateLink]</div>
<div>AS
BEGIN</div>
<div> SET NOCOUNT ON;</div>
<div> --创建远程链接
 DECLARE @linkname VARCHAR(100)
 DECLARE @linkip VARCHAR(100)
 DECLARE @linksa VARCHAR(100)
 DECLARE @linkpassword VARCHAR(100)
 DECLARE @sql NVARCHAR(4000)
 DECLARE @itemCur CURSOR
 SET @itemCur = CURSOR FOR 
 SELECT [LinkName],[LinkIP],[LinkSa],[LinkPassword] FROM dbo.LinkConfig WHERE State =1</div>
<div> OPEN @itemCur
 FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword
 WHILE @@FETCH_STATUS=0
 BEGIN
 --正在处理
 PRINT @linkname</div>
<div> --不存在
 IF NOT EXISTS (SELECT * FROM sys.servers WHERE name = @linkname)
 BEGIN
 --创建链接方式1
 SET @sql = '
 EXEC master.dbo.sp_addlinkedserver @server = <a href="mailto:N'''+@linkname+'''">N'''+@linkname+'''</a>, @srvproduct=N''SQL Server'''
 EXEC(@sql)</div>
<div> --设置密码
 SET @sql = '
 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = <a href="mailto:N'''+@linkname+'''">N'''+@linkname+'''</a>, @locallogin = NULL , @useself = N''False'', @rmtuser = <a href="mailto:N'''+@linksa+'''">N'''+@linksa+'''</a>, @rmtpassword = <a href="mailto:N'''+@linkpassword+''''">N'''+@linkpassword+''''</a>
 EXEC(@sql)</div>
<div>-- --创建链接方式2
-- SET @sql = '
-- EXEC master.dbo.sp_addlinkedserver @server = <a href="mailto:N'''+@linkname+'''">N'''+@linkname+'''</a>, @srvproduct=N'''+@linkname+''', @provider=N''SQLNCLI'', @datasrc=N'''+@linkip+''''
-- EXEC(@sql)
--
-- --设置密码
-- SET @sql = '
-- EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = <a href="mailto:N'''+@linkname+'''">N'''+@linkname+'''</a>, @locallogin = NULL , @useself = N''False'', @rmtuser = <a href="mailto:N'''+@linksa+'''">N'''+@linksa+'''</a>, @rmtpassword = <a href="mailto:N'''+@linkpassword+''''">N'''+@linkpassword+''''</a>
-- 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</div>
<div> FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword
 END</div>
<div> CLOSE @itemCur
 DEALLOCATE @itemCur</div>
<div>END</div>
<div>--删除远程链接sp
-- =============================================
-- Author: <Viajar>
-- Create date: <2012.01.05>
-- Description: <删除远程链接>
-- =============================================
CREATE PROCEDURE [dbo].[sp_DropLink]</div>
<div>AS
BEGIN</div>
<div> SET NOCOUNT ON;</div>
<div> --删除远程链接
 DECLARE @linkname VARCHAR(100)
 DECLARE @linkip VARCHAR(100)
 DECLARE @linksa VARCHAR(100)
 DECLARE @linkpassword VARCHAR(100)
 DECLARE @sql NVARCHAR(4000)
 DECLARE @itemCur CURSOR
 SET @itemCur = CURSOR FOR 
 SELECT [LinkName],[LinkIP],[LinkSa],[LinkPassword] FROM dbo.LinkConfig WHERE State =1</div>
<div> OPEN @itemCur
 FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword
 WHILE @@FETCH_STATUS=0
 BEGIN
 --正在处理
 PRINT @linkname</div>
<div> --删除链接
 SET @sql = '
 IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = <a href="mailto:N'''+@linkname+'''">N'''+@linkname+'''</a>)
 BEGIN
 EXEC master.dbo.sp_dropserver @server=N'''+@linkname+''', @droplogins=''droplogins''
 END'
 EXEC(@sql)</div>
<div> FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword
 END</div>
<div> CLOSE @itemCur
 DEALLOCATE @itemCur</div>
<div>END
</div>
试试其它关键字
　链接服务器　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)