October 14, 2011 at 5:31 am
I am struggling with an overview of jobhistory. I am so far as to convert job_runtime and job_rundate into a valid Datetime format and to load the current jobhistory into a user-table. What I cannot accomplish until now is to select only the new entries in the sysjobhistory table compared to what is already loaded in my user-table. I am struggling with the aggregate limits and the group by restrictions, and the script to STUFF the rundate and runtime values into a datetime format.
I've Googled around, but have not been able to just get these new records. Any link known on how to accomplish this?
The script I use to initially fill the user-table goes like this:
INSERT INTO tJobReport (
[server],
[jobname],
[description],
[enabled],
[runstatus] ,
[runduration],
[rundatetime]
)
select distinct 'AEBDBS01', j.Name , j.description, j.enabled, h.run_status,
stuff(stuff(right('000000' + convert(varchar(6),h.run_duration),6),5,0,':'),3,0,':'),
convert(datetime,cast(h.run_date as varchar(8)) + ' ' + stuff(stuff(right('000000' + convert(varchar(6), cast(h.run_time as varchar(6))),6),5,0,':'),3,0,':'))
from [AEBDBS01].msdb.dbo.sysJobHistory h, [AEBDBS01].msdb.dbo.sysJobs j
where j.job_id = h.job_id
Greetz,
Hans Brouwer
October 19, 2011 at 12:17 pm
Just quickly looking at your problem. Why don't you include the job_id in your user-table, and then left outer join on both the job_id and the run_date and run_time fields. If you can wait until tonight, i will create the script. I don't really have the time now.
October 20, 2011 at 2:42 am
Problem is solved with an EXCPT construction.
Tnx for answering.
Greetz,
Hans Brouwer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply