Polling and determine the status of a job?

Question

Post reply

Polling and determine the status of a job?

info 58414

Hall of Fame

Points: 3386
More actions
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:

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Nicole

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

John

Tom_Hogan Hall of Fame Points: 3897 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 3

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)

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

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)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

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