November 11, 2008 at 6:06 am
Hello,
Can anyone help me on the following query?
I need to get the history of jobs on a specific date.
I've got the following query but it return an error on the output because of date convertion :o|
SELECT
j.name as JobName,
NextRunDate =
DATENAME(dw, CONVERT(CHAR(8), next_run_date, 112))
+ ', '
+ DATENAME(m, CONVERT(CHAR(8), next_run_date, 112))
+ ' '
+ CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), next_run_date, 112)))
+ ', '
+ CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), next_run_date, 112))),
+ STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':')
FROM
msdb..sysjobs j,
msdb..sysjobschedules js
WHERE
j.job_id = js.job_id
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
Thanks and regards,
JMSM 😉
November 11, 2008 at 6:28 am
Try this:
SELECT j.name AS JobName,
SUBSTRING(CONVERT(CHAR(8),next_run_date),7,2) + '-'+
SUBSTRING(CONVERT(CHAR(8),next_run_date),5,2) + '-' +
SUBSTRING(CONVERT(CHAR(8),next_run_date),1,4)+ ' ' +
SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),next_run_time),6),1,2) + ':'+
SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),next_run_time),6),3,2) + ':' +
SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),next_run_time),6),5,2)
AS NextRun
FROM
msdb..sysjobs j JOIN
msdb..sysjobschedules js
ON j.job_id = js.job_id
[font="Verdana"]Markus Bohse[/font]
November 11, 2008 at 7:58 am
Thanks for the answer,
But can you(anyone) tell me one query to get job history of all jobs that were running on a specific date (November 09(nine))
Thanks and regards,
JMSM 😉
November 11, 2008 at 10:55 am
This script might not include exactly the columns you're looking for but it should help to start you up. Ideally replace GETDATE() at the end with some variables and use it as a stored procedure. Also of course instead of GETDATE() it's better to round it to a full day.
Use msdb
GO
SELECT j.name as Jobname,
j.Description,
l.name as Owner,
s.srvname as Server,
[Status] = CASE
WHEN run_status = 1 THEN 'Succeeded'
WHEN run_status = 2 THEN 'Retry'
WHEN run_status = 3 THEN 'Canceled'
WHEN run_status = 4 THEN 'In progress'
WHEN run_status = 0 THEN 'Failed'
ELSE 'Status not available'
END,
CASE len(h.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(h.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(h.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(h.run_duration,3),1)
+':' + right(h.run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(h.run_duration,5),1)
+':' + Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(h.run_duration,6),2)
+':' + Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
END as 'Duration'
FROM sysjobs j
LEFT JOIN sysoperators o
ON j.notify_email_operator_id = o.id
JOIN master..syslogins l
ON j.Owner_sid = l.sid
JOIN sysjobservers s1
ON j.job_id = s1.job_id
JOIN master..sysservers s
ON s1.server_id = s.srvid
LEFT JOIN sysjobschedules sch
ON j.job_id = sch.Job_id
LEFT JOIN dbo.sysjobhistory h
ON j.job_id = h.Job_id
WHERE SUBSTRING(CONVERT(char(8),h.Run_date),5,2) + '-'+
SUBSTRING(CONVERT(char(8),h.Run_date),7,2) + '-' +
SUBSTRING(CONVERT(char(8),h.Run_date),1,4)+ ' ' +
SUBSTRING(RIGHT('000000' + CONVERT(varchar(6),h.run_time),6),1,2) + ':'+
SUBSTRING(RIGHT('000000' + CONVERT(varchar(6),h.run_time),6),3,2) + ':' +
SUBSTRING(RIGHT('000000' + CONVERT(varchar(6),h.run_time),6),5,2)BETWEEN GETDATE() and DATEADD(dd,-1,Getdate())
[font="Verdana"]Markus Bohse[/font]
November 11, 2008 at 11:22 am
[font="Courier New"]SELECT Job.[name], Hst.[sql_message_id], Hst.[message] , Hst.[run_date], Hst.[run_time], run_duration
FROM [msdb].dbo.sysjobhistory Hst
INNER JOIN [msdb].dbo.sysjobs Job ON Hst.[job_id] = Job.[job_id]
where convert(varchar(8), GETDATE()-1,112)= Hst.[run_date]
and name not like '%replication%' and name not like '%distribution%' -- omit all the replication jobs
ORDER BY /*Job.[name],*/Hst.[run_date] DESC, Hst.[run_time] DESC[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply