Post reply

Polling and determine the status of a job?

  • August 14, 2017 at 9:29 am

    #324850

    Hi,

    I want to get a message from a RemoteServer when a certain SQL job is successfully terminated. After the job is finished, I want to access this data remotely. This should be done immediately after the job is finished. (While Loop with EXEC msdb.dbo.sp_help_job, OR MessageQueue OR ...?)
    SQL2016 to SQL2008R2

    Regards
    Nicole
    :Whistling:

  • August 14, 2017 at 9:35 am

    #1955180

    Nicole

    How about a trigger on sysjobhistory?  I think that's legal, although I've never tried it.

    John

  • August 15, 2017 at 7:33 am

    #1955288

    You could set up an Operator on the SQL Agent with the appropriate email(s) / distribution list that is notified when the job completes successfully.

  • August 22, 2017 at 8:32 pm

    #1956261

    info 58414 - Monday, August 14, 2017 9:29 AM

    Hi,

    I want to get a message from a RemoteServer when a certain SQL job is successfully terminated. After the job is finished, I want to access this data remotely. This should be done immediately after the job is finished. (While Loop with EXEC msdb.dbo.sp_help_job, OR MessageQueue OR ...?)
    SQL2016 to SQL2008R2

    Regards
    Nicole
    :Whistling:

    Add an sp_send_dbmail job step to the end of the job with an attachment or maybe even as an HTML table embedded in the body.  If you lookup sp_send_dbmail in Books Online, example "C" shows you how.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 22, 2017 at 8:34 pm

    #1956262

    Jeff Moden - Tuesday, August 22, 2017 8:32 PM

    info 58414 - Monday, August 14, 2017 9:29 AM

    Hi,

    I want to get a message from a RemoteServer when a certain SQL job is successfully terminated. After the job is finished, I want to access this data remotely. This should be done immediately after the job is finished. (While Loop with EXEC msdb.dbo.sp_help_job, OR MessageQueue OR ...?)
    SQL2016 to SQL2008R2

    Regards
    Nicole
    :Whistling:

    Add an sp_send_dbmail job step to the end of the job with an attachment or maybe even as an HTML table embedded in the body.  If you lookup sp_send_dbmail in Books Online, example "C" shows you how.

    p.s.  If you can't setup email on that remote server for some reason, lemme know.  I've got a store procedure for that problem, as well.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 23, 2017 at 12:06 am

    #1956302

    info 58414 - Monday, August 14, 2017 9:29 AM

    Hi,

    I want to get a message from a RemoteServer when a certain SQL job is successfully terminated. After the job is finished, I want to access this data remotely. This should be done immediately after the job is finished. (While Loop with EXEC msdb.dbo.sp_help_job, OR MessageQueue OR ...?)
    SQL2016 to SQL2008R2

    Regards
    Nicole
    :Whistling:

    In this case I think "message" for you means "programmatic signal", not necessarily an email message.

    Some custom code added to the job running on the SQL 2016 that writes a Service Broker message destined for a queue on the SQL 2008R2 instance would be easy enough to implement provided you have control over the code the SQL 2016 job is executing. If you need an independent signal to be produced by the SQL 2016 instance then remotely polling for the SQL Agent Job status to change might be your best bet.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply