Saturday, February 02, 2013 8:10 AM
Hi I have two jobs ABC and XYZ
Now I want to make condition like this
Check abc job run today
If yes then run XYZ
If No then run ABC
Please give me a query i think there must be some table from MSDB that helps us but i am not sure which one
Saturday, February 02, 2013 8:26 AMModerator
The following blog post is on the programmatic execution of jobs:
Check the query on job history.
Saturday, February 02, 2013 2:49 PMHi,
select Job_name, run_date,run_status from msdb.sysjobhistory sjh inner join msdb.sysjobs s on sjh.job_id=sj.job_idwhere sj.name='<<JobName>>'
this would give you the last run status and run date for a job
You can have a SP that checks the status and depending on the run status you can start either of the job using sp_start_job
- Marked As Answer by Iric WenModerator Monday, February 18, 2013 1:47 AM
Saturday, February 02, 2013 4:30 PM
HiPowerShell scripts to give various pieces of information and put together a PowerShell script that could be run as a SQL Agent job to periodically report the statuses of SQL Agent jobs. Eventually, I think Colin went with a different solution, but I figured I would go ahead and post the PowerShell script that I came up with.
CREATE TABLE #JobsRun ( ServerName nvarchar(128), Job_Name nvarchar(128), Run_Date datetime, Job_Duration time(7), Run_Status varchar(50), Sample_Date datetime ); insert into #JobsRun select @@SERVERNAME AS ServerName ,j.name Job_Name ,(msdb.dbo.agent_datetime(jh.run_date,jh.run_time)) As Run_Date ,substring(cast(run_duration + 1000000 as varchar(7)),2,2) + ':' + substring(cast(run_duration + 1000000 as varchar(7)),4,2) + ':' + substring(cast(run_duration + 1000000 as varchar(7)),6,2) Job_Duration ,case when run_status = 0 then 'Failed' when run_status = 1 then 'Succeed' when run_status = 2 then 'Retry' when run_status = 3 then 'Cancel' when run_status = 4 then 'In Progress' end as Run_Status ,GETDATE() As Sample_Date FROM msdb.dbo.sysjobhistory jh join msdb.dbo.sysjobs j on jh.job_id = j.job_id where step_id = 0 and enabled = 1 order by cast(cast(run_date as char) + ' ' + substring(cast(run_time + 1000000 as varchar(7)),2,2) + ':' + substring(cast(run_time + 1000000 as varchar(7)),4,2) + ':' + substring(cast(run_time + 1000000 as varchar(7)),6,2) as datetime) desc delete from MgtServer.DBA_Console.dbo.LastJobStatus where ServerName = '$server' -- Change 'MgtServer' to the name of whatever the SQL Server is in -- your env that will house the LastJobStatus table which stores the -- results of this script insert into MgtServer.DBA_Console.dbo.LastJobStatus (ServerName, Job_Name, Run_Date, Job_Duration, Run_Status, Sample_Date) select jr.ServerName, jr.Job_Name, jr.Run_Date, jr.Job_Duration, jr.Run_Status, jr.Sample_Date from #JobsRun jr where Run_Date = ( select max(jr1.Run_Date) from #JobsRun jr1 where jr1.Job_Name = jr.Job_Name) drop table #JobsRun
Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/