重命名SQL中的Agent代理
程序员文章站
2022-04-08 16:31:38
...
重命名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'