Results 1 to 9 of 9
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: T-SQL to change notification in SQL Agent jobs

    Hi all,

    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!

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Look up sp_update_job, that should do the trick.

    Grtz, Lex

  3. #3
    Join Date
    May 2003
    Posts
    369
    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.

    Syntax

    sp_update_job [@job_id =] job_id | [@job_name =] 'job_name'
    [, [@new_name =] 'new_name']
    [, [@enabled =] enabled]
    [, [@description =] 'description']
    [, [@start_step_id =] step_id]
    [, [@category_name =] 'category']
    [, [@owner_login_name =] 'login']
    [, [@notify_level_eventlog =] eventlog_level]
    [, [@notify_level_email =] email_level]
    [, [@notify_level_netsend =] netsend_level]
    [, [@notify_level_page =] page_level]
    [, [@notify_email_operator_name =] 'email_name']
    [, [@notify_netsend_operator_name =] 'netsend_operator']
    [, [@notify_page_operator_name =] 'page_operator']
    [, [@delete_level =] delete_level]
    [, [@automatic_post =] automatic_post]

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would script out the jobs and do a replace
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    First you have to set up the operator, then you will use the following:
    Code:
    sp_update_job [@job_id =] job_id | [@job_name =] 'job_name'
    @notify_email_operator_name =] 'email_name'

  6. #6
    Join Date
    May 2003
    Posts
    369
    Hi,

    How does this look?

    CREATE PROC pr_chgjob_email AS

    DECLARE
    @job_id number ,
    @job_name varchar(200),
    @notify_email_operator varchar(200)

    BEGIN
    sp_update_job @job_id=10 @job_name='truncate_tables'
    @notify_email_operator_name='DBA'
    END

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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:

    Code:
    exec msdb.dbo.sp_update_job 
      @job_name='truncate_tables'
      ,@notify_email_operator_name='DBA'
    of course, you have to do this 200 times so you might do this instead:
    Code:
    select 'exec msdb.dbo.sp_update_job @job_name='''+name+'''
    ,@notify_email_operator_name=''DBA'''
    from msdb.dbo.sysjobs
    and execute the result.

  8. #8
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Yep, the second query is the most easy way to do this.

    Remember to have an operator with this name!

  9. #9
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    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.

    Good luck,
    Hope this helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •