0

This question is similar, but not quite what I'm looking for: Executing SQL Server Agent Job from a stored procedure and returning job result

I'd like to run a job and return the instance ID for recording in a separate table for reporting. We get daily extracts of files and when we've pre-processed the files we kick off a SQL Agent Job. The same job might get kicked off multiple times in short order, so we need the instance id immediately.

Furthermore, I'm showing the results on a small dash that could really use a "Job Completion Time" column. Right now I've got a separate page that only shows the most recent job status. There's no way to connect the job w/ the completion date.

I suppose that running the job and immediately querying for the highest instance ID of that job would do the trick, but I was really hoping for something a bit more foolproof.

Community
  • 1
  • 1
Chris Pfohl
  • 18,220
  • 9
  • 68
  • 111
  • According to this, doesn't seem possible - http://dba.stackexchange.com/questions/33504/is-there-a-way-to-get-instance-id-from-msdb-dbo-sysjobhistory-during-the-executi – EkoostikMartin May 14 '14 at 14:14
  • Didn't even think to look on dba... – Chris Pfohl May 14 '14 at 14:19
  • I think you are going to have to poll the history table for highest instance_id. – EkoostikMartin May 14 '14 at 14:22
  • I also record the time that I kick off the agent job, I'm probably just going to match the job start time that I have recorded to the job start time in the history table with a few seconds flexibility...It's not 100% guaranteed to be unique, but it should help. – Chris Pfohl May 14 '14 at 14:29

1 Answers1

0

To get the details of Jobs executed you can use the following query aginst dbo.sysjobs and dbo.sysjobhistory tables in MSDB database.

select 
 j.name as 'JobName',
 h.instance_id,
 run_date,
 run_time,
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 run_duration
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1  --Only Enabled Jobs
order by run_date, RunDateTime desc

Result Set

╔═══════════════════════════════════════════════════╦═════════════╦══════════╦══════════╦═════════════════════════╦══════════════╗
║                      JobName                      ║ instance_id ║ run_date ║ run_time ║       RunDateTime       ║ run_duration ║
╠═══════════════════════════════════════════════════╬═════════════╬══════════╬══════════╬═════════════════════════╬══════════════╣
║ sysDatabase_weekly_Full_Backup_MyServer.Subplan_1 ║        1769 ║ 20130910 ║   110052 ║ 2013-09-10 11:00:52.000 ║            3 ║
║ sysDatabase_weekly_Full_Backup_MyServer.Subplan_1 ║        1770 ║ 20130910 ║   110052 ║ 2013-09-10 11:00:52.000 ║            3 ║
║ sysDatabase_weekly_Full_Backup_MyServer.Subplan_1 ║        2025 ║ 20130915 ║    20001 ║ 2013-09-15 02:00:01.000 ║            4 ║
║ sysDatabase_weekly_Full_Backup_MyServer.Subplan_1 ║        2026 ║ 20130915 ║    20000 ║ 2013-09-15 02:00:00.000 ║            5 ║
║ sysDatabase_weekly_Full_Backup_MyServer.Subplan_1 ║        2415 ║ 20130922 ║    20000 ║ 2013-09-22 02:00:00.000 ║           17 ║
║ sysDatabase_weekly_Full_Backup_MyServer.Subplan_1 ║        2416 ║ 20130922 ║    20000 ║ 2013-09-22 02:00:00.000 ║           17 ║
║ sysDatabase_weekly_Full_Backup_MyServer.Subplan_1 ║        8804 ║ 20130929 ║    20000 ║ 2013-09-29 02:00:00.000 ║            4 ║
╚═══════════════════════════════════════════════════╩═════════════╩══════════╩══════════╩═════════════════════════╩══════════════╝

To see some more cool queries about how to query sql server agent's Job history read this Querying SQL Server Agent Job History Data by Chad Churchwell

M.Ali
  • 67,945
  • 13
  • 101
  • 127