November 23, 2010 at 7:28 pm
USE msdb
Go
I want to get the SQL job agent history for less than 24 hours or for the current day. Can someone please help me to complete the script becuase the getdate() is giving the arithimetic error. please help.
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
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 ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
ORDER BY j.name, h.run_date, h.run_time
GO
November 23, 2010 at 7:43 pm
How about this:
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
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 ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where run_date >= CONVERT(varchar(10),getdate(),112)
ORDER BY j.name, h.run_date, h.run_time
November 23, 2010 at 7:47 pm
I tried that as well and I got the same arithmetic error.
November 23, 2010 at 10:25 pm
hydbadrose (11/23/2010)
I tried that as well and I got the same arithmetic error.
can you please post the error message as it is working fine for me. I am running it on SQL Server 2008 R2
November 24, 2010 at 11:22 am
I copied and pasted your script instead of typing by myself and its working. Thank you so much for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply