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.
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.