重命名SQL中的Agent代理

2015/11/09 37127

清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
--(SQL Server 2000 only)
 
use msdb
go
 
if exists (select * from sysobjects where name = N'sp_sqlagent_rename' and type ='P')
    drop proc dbo.sp_sqlagent_rename
go
 
create proc dbo.sp_sqlagent_rename
@old_server nvarchar(30)
as
 
set nocount on
 
if (charindex(N'8.00', @@version, 0) = 0)
begin
    raiserror('sp_sqlagent_rename is only required on SQL Server 2000, procedure will abort', 11, 1)
end
 
if (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 0)
begin
    raiserror('sp_sqlagent_rename can only be used by sysadmin role members, procedure will abort', 11, 1)
end
 
declare @new_server nvarchar(30)
select @new_server = convert(nvarchar(30), serverproperty(N'servername'))
 
if exists(select * from msdb.dbo.sysjobs where upper(originating_server) = upper(@old_server))
begin
    update msdb.dbo.sysjobs
    set originating_server = @new_server
    where originating_server = @old_server
 
    raiserror('sp_sqlagent_rename, %d entries updated from %s to %s', 10, 1,
        @@rowcount,  @old_server, @new_server )
end
else
begin
    raiserror('sp_sqlagent_rename, no entries found for @old_server = %s, 0 rows are updates', 10, 1, @old_server)
end
go
 
-- sample usage
-- exec msdb.dbo.sp_sqlagent_rename @old_server = 'GERTD00\DEV'