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

发布于 2024-07-09 05:33:54 字数 223 浏览 8 评论 0原文

我有很多工作都有一个步骤,就是向一组人员发送一封特定的电子邮件。 该收件人列表是在步骤命令中硬编码的,我需要在所有作业中从该列表中删除一个人。

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

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

干杯。

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.

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.

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

Cheers.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

迟到的我 2024-07-16 05:33:54

您可以尝试直接更新保存 SQL Server 作业信息的系统表。 与您相关的内容是:

msdb.dbo.SysJobs
msdb.dbo.SysJobSteps

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

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 '%[email protected]%'

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

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:

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 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 '%[email protected]%'

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.

夜未央樱花落 2024-07-16 05:33:54

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

/*
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'

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'
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文