Home >Database >Mysql Tutorial >重命名SQL中的Agent代理

重命名SQL中的Agent代理

WBOY
WBOYOriginal
2016-06-07 14:54:281199browse

重命名SQL中的Agent代理 Agent --(SQL Server 2000 only) use msdbgoif exists (select * from sysobjects where name = N'sp_sqlagent_rename' and type ='P') drop proc dbo.sp_sqlagent_renamegocreate proc dbo.sp_sqlagent_rename @old_server nvarchar(

重命名SQL中的Agent代理 Agent
--(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'
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn