none
SQL Job Query help not from job history or from Job activity

    Question

  • 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:10 AM

Answers

  • Hi,
    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


    Regards
    Satheesh

    Saturday, February 02, 2013 2:49 PM
  • Hi

    PowerShell 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/


    Saturday, February 02, 2013 4:30 PM

All replies

  • The following blog post is on the programmatic execution of jobs:

    http://www.sqlusa.com/bestpractices2005/startjobfromstoredproc/

    Check the query on job history.


    Kalman Toth SQL 2008 GRAND SLAM
    Paperback/Kindle: SQL Server 2012 Pro

    Saturday, February 02, 2013 8:26 AM
  • Hi,
    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


    Regards
    Satheesh

    Saturday, February 02, 2013 2:49 PM
  • Hi

    PowerShell 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/


    Saturday, February 02, 2013 4:30 PM