sql run scheduled task and check for completion

• Meatloaf

  SSCrazy

  Points: 2564

  More actions

  November 6, 2015 at 1:07 pm

  #302161

  Hello,

  I am looking to create a Stored Procedure that simply runs a Scheduled Task that they knows when the job has completed.

  To call the Job I am using:

  EXEC msdb.dbo.sp_start_job @job_name='MY_JOB_NAME'

  As a next step is there a way to check if the Scheduled Job has completed and keep checking until the job completes via a query?

• SoHelpMeCodd

  SSCertifiable

  Points: 5871

  More actions

  November 8, 2015 at 12:40 am

  #1838444

  Slight rewrite of http://qa.sqlservercentral.com/Forums/Topic1733134-3077-1.aspx

  --declare @JobName sysname ='Your Job Name'

  SELECT *

  FROM msdb.dbo.sysjobs_view j

  JOIN msdb.dbo.sysjobactivity a

  ON (j.job_id = a.job_id)

  JOIN (SELECT MAX(session_id) AS session_id FROM msdb.dbo.syssessions) s

  ON s.session_id = a.session_id

  WHERE j.enabled = 1

  AND a.run_requested_date IS NOT NULL

  AND a.stop_execution_date IS NULL

  --AND j.name = @JobName