Hello all,
I need to create a report on a certain Job on my server. It should look exactly like the "View History" window.
to do that, i need to find a way to group the logged steps by the job executions. The problem is that in the sysjobhistory table there's only a instance_id field that runs incrementally, with no way to identify the execution…
any ideas?
You'll need to join so sysjobs and sysoperators if you want the Job Name and Operators that were emailed, net sent or paged.
You might be able to group the run_date and run_time together and use this for your "key" to group the job steps together. Note: run_time is in a 24 hour format and is not left zero filled (i.e. 10001 is 1:00:01 am, 152531 is 15:25:31 pm).
convert(varchar(8), run_date) + right('000000' + convert(varchar(6), run_time), 6) -->> will give you the date and hhmmss the step executed (i.e. 20061225010001).
You could parse the results (maybe using hours and/or minutes) to group the steps together.
The above is a quick and dirty and depending upon how often and how long your jobs run, may not work.
Since the format of job history is consitent with step_id of 0 as the job completion step, you can write a stored procedure and use a cursor to group the steps (read all the steps and "break" each time a step_id of 0 is encountered).
|||
Thanks,
The first way you wrote will not work since you cannot group on Running Date & Time - this group will not identify all the steps in a certain run in the same group.
I also thought of the second way, but it seemed too costly.
How does the "view job history" window know how to separate the job executions?
|||Liran, that is why I wrote that the first step "may not work". If your job ran once each hour, started at 1 minute past the hour and ran for less than 59 minutes every time, the first method would work since each step would have the same date and hour time stamp. Obviously this won't work if your job starts at 59 minutes after the hour and runs for more than one minute or runs for more than one hour.
Not sure how the "view job history" works but would imagine it does a similar activity as what I described for writing a stored procedure. Since you can't have the same job name executing at the same time on the same server, the starting date/time of the job and each of it's steps will be in chronological order; doesn't matter if the job runs more than one our or spans over multiple days.
Another option might be to add the duration (from step_id = 0) to the start date/time to get the ending date/time of the job (NOT the steps). You could then choose any of the rows in sysjobhistory that fall between the start date/time and the calculated ending date/time of the job.
You said you had an idea, why don't you describe it and we might be able to expand upon it...
|||
OK, Now I understood what you meant, but this method is not relevant to me since this job runs for more then 1 hour with 1 min between each run…
About the second Idea, I meant I also thought of the second idea you wrote (with the procedure and cursor) but it seems to me too costly to be executed every time the report is viewed…
Thanks, I just thought there's somewhere a "job running instance id" that I'm overlooking…
|||Pls chek the following post..It may help you
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033004&SiteID=1
