Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: How can I grab a notification email address from a job?

    Is there a handy-dandy way for me to be able, from a stored procedure, to capture the notification email address for a user associated with a job?

    I need to send an email out from a stored procedure, and I want it to be set up to send to the same user that is set up in the job that calls the stored procedure.

    the same notification stored proc will be used in multiple jobs, but the jobs are already set up with a notification email to be sent to the defined users "on completion" (error or not). I want a separate email to be sent, but I want to use xp_Sendmail because I have to perform a select who's output I want to appear in the body of the email (and unless I am missing something, I have no control over the body of the standard "job completion" email).

    I would prefer to NOT have to "hard code" the user email address in the stored proc that calls xp_sendmail, OR to put it in some user table in the database, when the user I want to send it to will always be the same one defined in at the JOB level in Enterprise Manager.

    Any thoughts? Thanks in advance for your instantaneous and informative answer-packed responses!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm shooting from the hip on this, but I'd start with:
    Code:
    SELECT j.name, o.email_address
       FROM msdb.dbo.sysjobs AS j
       LEFT JOIN msdb.dbo.sysoperators AS o
          ON (o.id = j.notify_email_operator_id)
    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    That's what I was looking for, Oh Great One....

    Or something to that effect...I obviously need to get more accustomed to the sys tables...but tried as many ways as I could think of to find a reference to something like that in BOL...but alas, apparently didn't know what to search for *L*

    I guess I need to break down and read my SQL200 Unleashed book and learn a little about the architecture that is padding my paycheck *sigh*

    As always, thanks for the gentle nudge
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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