Unanswered: T-SQL to change notification in SQL Agent jobs
I am trying to write a T-SQL script to change about 200 SQL Agent jobs to send notification to a different email notification list. I could do this manually but it would take me a lot more time. Is there a way to script this out in T-SQL? Thanks!
Thanks but how would I setup the sp_update job in T-SQL script to change all of the email names from old DBA to new DBA email? I dont see a way to change the email address list for 200 jobs in a t-sql script using the sp_update_job.
1. you don't need to create your own sproc. why not just call sp_update_job directly?
2. also, you don't need to pass both @job_id and @job_name. one or the other will suffice.
3. Finally, @job_id is a guid, not an int.
so if you have an operator called DBA, and a job named 'truncate_tables' (sounds like a dangerous job to run, btw) you would just execute this:
Here is just another thought, but please correct me if my logic is wrong.
We have our notification operators setup to e-mail group accounts, instead of individual members. This way in the future if we need to add/remove members, we can do it through the group e-mail accounts instead of going through all 1800+ databases which we support.