如何批量修改所有sql server代理作业中的作业步骤命令

I have many jobs that have a step to send 1 specific email out to a list of people. That list of reciepients is hardcoded in the step command and I need to remove one person from that list, in all jobs.

我有很多工作,可以将1个特定的电子邮件发送到人员列表。在step命令中硬编码了reciepients列表,我需要在所有作业中从该列表中删除一个人。

How do I loop through ALL the jobs in the Sql Server Agent and modify the command text to find+replace a specific piece of text.

如何遍历Sql Server Agent中的所有作业并修改命令文本以查找+替换特定的文本。

I am using sql server 2005 and have already looked at sp_update_jobStep but doesn't appear to be exactly what i want.

我正在使用sql server 2005并且已经查看了sp_update_jobStep,但似乎并不是我想要的。

Cheers.

2 个解决方案

#1

You could try to update the System tables that hold the information on jobs of the SQL server directly. The relevant ones for you would be:

您可以尝试直接更新包含SQL服务器作业信息的系统表。对你来说相关的是:

msdb.dbo.SysJobs
msdb.dbo.SysJobSteps

If you have a specific email address to remove, you could update the Command field in the SysJobSteps table with a single UPDATE statement.

如果要删除特定的电子邮件地址,则可以使用单个UPDATE语句更新SysJobSteps表中的“命令”字段。

UPDATE SJS SET
    Command = REPLACE(Command, 'EmailAddress&TestDomain.Com', '')
FROM msdb.dbo.SysJobs SJ
INNER JOIN msdb.dbo.SysJobSteps SJS
    ON SJS.Job_Id = SJ.Job_Id
WHERE SJ.Originating_server = ..... -- Your server here
    AND SJS.Command LIKE '%EmailAddress@TestDomain.Com%'

It would be advisable to run the above query as a SELECT statement first, to test it returns only the job steps your are expecting to update.

建议首先将上述查询作为SELECT语句运行,以测试它只返回您期望更新的作业步骤。

#2

If anybody still looking for this then below should do. Not the finest code but gets the job done.

如果有人仍然在寻找这个,那么下面应该这样做。不是最好的代码,但完成工作。

/*
Replace the below statement with the actual keyword to be replaced:
INSERT INTO SP_Keyword VALUES
('sys.databases3', 'sys.databases5')
,('sysjobs3', 'sysjobs5')

Here we are replacing sys.databases3 and sysjobs3 
with sys.databases5 and sysjobs5 respectively.

Script only searches in 'TSQL', 'Powershell', 'CMDEXEC' job type.
*/

SET NOCOUNT ON

use msdb
GO

DECLARE @OldV VARCHAR(max) 
DECLARE @NEWV VARCHAR(max)
DECLARE @job_id2 varchar(max)
DECLARE @step_id2 varchar(10)
DECLARE @command2 varchar(max)
DECLARE @command3 varchar(300)

--CLEANUP TASKS
IF EXISTS(SELECT * FROM sys.tables where name = 'SP_Keyword')
    DROP TABLE SP_Keyword

CREATE TABLE SP_Keyword
(
old_keyword varchar(200),
new_keyword varchar(200)
)

--DEBUG STATEMENT
--PRINT 'SP_Keyword TABLE CREATED'

INSERT INTO SP_Keyword VALUES
('sys.databases3', 'sys.databases5')
,('sysjobs3', 'sysjobs5')

--DEBUG STATEMENT
--PRINT 'VALUES INSERTED FOR KEYWORD'

CREATE TABLE #jobs(
    [name] [sysname] ,
    [step_name] [sysname] ,
    [subsystem] [varchar](40) ,
    [command] [varchar](max) ,
    [keyword] [varchar](200) ,
    [database_name] [sysname] ,
    [enabled] [tinyint] ,
    [description] [varchar](512) ,
    [date_created] [datetime] ,
    [job_id] [uniqueidentifier] ,
    [step_id] [int]     
)

DECLARE db_cursor3 CURSOR FOR  
SELECT old_keyword FROM SP_Keyword
    OPEN db_cursor3   
    FETCH NEXT FROM db_cursor3 INTO @OldV

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        INSERT INTO #jobs
        SELECT a.name as job_name, b.step_name, b.subsystem, 
        b.command, @OldV as 'keyword', b.database_name, a.enabled, 
        a.description, a.date_created, b.job_id, b.step_id
        FROM [dbo].[sysjobsteps] as b
        INNER JOIN sysjobs as a
        ON a.job_id = b.job_id
        WHERE [command] LIKE '%'+@OldV+'%'
        AND [subsystem] IN ('TSQL', 'Powershell', 'CMDEXEC')

        FETCH NEXT FROM db_cursor3 INTO @OldV
    END   

    CLOSE db_cursor3   
    DEALLOCATE db_cursor3

IF NOT EXISTS(SELECT * FROM #jobs)
    BEGIN
    PRINT 'NO JOBS FOUND TO BE REPLACED'
    GOTO SKIPPER
    END
--SELECT name as job_name, step_name, command, keyword, database_name, enabled, description, date_created FROM #jobs

    DECLARE db_cursor3 CURSOR FOR  
    SELECT * FROM SP_Keyword

    OPEN db_cursor3   
    FETCH NEXT FROM db_cursor3 INTO @OldV,@newV

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        UPDATE #jobs SET command=REPLACE(command,@oldV,@NewV)
          WHERE command LIKE '%'+@OldV+'%'
           FETCH NEXT FROM db_cursor3 INTO @oldV,@NewV
    END   

    CLOSE db_cursor3   
    DEALLOCATE db_cursor3

    print 'COMMAND TO REPLACE JOB STEPS IS BEING EXECUTED'

    DECLARE db_cursor3 CURSOR FOR  
    SELECT job_id, step_id, command FROM #jobs

    OPEN db_cursor3   
    FETCH NEXT FROM db_cursor3 INTO @job_id2,@step_id2, @command2

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        SET @command3 = 'sp_update_jobstep @job_id = '''+@job_id2+''' ,@step_id = '+@step_id2+', @command = '''+@command2+''''
        PRINT @command3
        EXEC(@command3)
        FETCH NEXT FROM db_cursor3 INTO @job_id2,@step_id2, @command2
    END   

    CLOSE db_cursor3   
    DEALLOCATE db_cursor3

SKIPPER:
DROP TABLE #jobs
DROP TABLE SP_Keyword
PRINT 'CLEANUP DONE'
PRINT 'PROGRAM COMPLETE'

#1