How to bulk-amend the job step command in ALL sql server agent jobs

Question

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.

Answer 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:

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.