March 8, 2014 at 9:04 am
Hi SQL Gurus :-),
I have a query that returns the jobs that have completed for the nightly processing.
If I run the query it does not reflect jobs that are in progress.
How can the following query be modified to reflect jobs that have started but have not completed?
Any input would be much appreciated. Thank you.
SELECT DISTINCT j.Name AS Job_Name,
msdb.dbo.Agent_datetime(Run_Date, Run_Time) AS Run_Datetime,
Dateadd(second, Datediff(second, 0, msdb.dbo.Agent_datetime(19000101, Run_Duration)), msdb.dbo.Agent_datetime(Run_Date, Run_Time)) AS Run_Endtime,
Stuff(Stuff(RIGHT('000000'
+ Cast(Run_Duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Run_Duration,
CASE h.Run_Status
WHEN 0 THEN 'failed'
WHEN 1 THEN 'Succeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS Execution_Status
FROM msdb..Sysjobhistory h
INNER JOIN msdb..Sysjobs j
ON h.Job_id = j.Job_id
WHERE msdb.dbo.Agent_datetime(Run_Date, Run_Time) >= DATEADD(dd,DATEDIFF(dd,0,GETDATE())-1,CAST('18:00' AS DATETIME))
ORDER BY Run_Datetim
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 8, 2014 at 3:05 pm
Include a JOIN to msdb.dbo.sysjobactivity and ORDER BY the run_requested_date DESC - it will return 0 for any job step that is currently executing - example: (SELECT TOP 1 ISNULL(last_executed_step_id, 0)
FROM msdb.dbo.sysjobactivity
WHERE job_id = @JobID
ORDER BY run_requested_date DESC )
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 8, 2014 at 5:26 pm
Thanks but can I get the entire code?
It does not work.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 9, 2014 at 9:40 am
What doesn't work?
That's a derived table that Jessie posted, you'd put it in the join clause and join to it like a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 9, 2014 at 11:42 am
GilaMonster (3/9/2014)
What doesn't work?That's a derived table that Jessie posted, you'd put it in the join clause and join to it like a table.
I appreciate it. Could I get the entire syntax?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 9, 2014 at 12:38 pm
http://technet.microsoft.com/en-us/library/ms177634.aspx
FROM (derived_table_query) AS <derived_table_alias>
ON derived_table_alias.column = other_table.other_column
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 9, 2014 at 2:27 pm
How about this? You could just both result sets into a temp table or use CTE to select out the results SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id, 0) + 1 AS current_executed_step_id,
Js.step_name
FROM
msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id, 0) + 1 = js.step_id
WHERE
ja.session_id = (SELECT TOP 1 session_id
FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC)
AND start_execution_date IS NOT NULL
AND stop_execution_date IS NULL;
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply