Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Job Status Report with Error Detail


Job Status Report with Error Detail

Author
Message
l543123
l543123
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 175
makes sense Smile thanks for the reply.
alen teplitsky
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
l543123 (12/15/2009)
nice one.. but why do you need this.. cant we just use email notification when jobs fail and use SSMS - job activity monitor to monitor jobs.


i like seeing everything in one email daily or reading it on the website. otherwise you get email clutter.

and if i'm in the mood on the train ride to work, i'll read the report on my iphone
alen teplitsky
alen teplitsky
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1595 Visits: 4621
nice post

there was a similar article a few weeks ago that i extended for the same thing. big difference is i import sysjobs and jobhistory daily for all monitored servers and my report has the reason for the failure in the report.

later on i plan to extend it with data like average run time to give a heads up if a job suddenly takes longer to run.

and to get around the annoying int to date conversion i added a date column myself
Gator650
Gator650
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 79
A shared datasource with SA credentials on a report server.... what's the worst that could happen? Wink

While I understand the simplicity and brevity you were going for for the purposes of this article.... wouldn't it at least make more sense to grab the data with a job, dump into a repository and have the report just use read only credentials on that repository?

Just having an account be owner over MSDB didn't give you the data you needed?

Near Kindest Regards, Gator
royce.bacon
royce.bacon
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 991
Gator-221056 (12/15/2009)
A shared datasource with SA credentials on a report server.... what's the worst that could happen? Wink

While I understand the simplicity and brevity you were going for for the purposes of this article.... wouldn't it at least make more sense to grab the data with a job, dump into a repository and have the report just use read only credentials on that repository?

Just having an account be owner over MSDB didn't give you the data you needed?


I know that isn't the ideal situation from a security perspective. I tried various alternatives that didn't seem to work and I don't recall all of what I tried at this time. I do have a special account "ReportViewer" that I use for other reports that I was planning to get it, or maybe a different one, to work for this report when I have a chance.
PSB1
PSB1
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 665
Really really useful. Most of our jobs are set to move to next step on failure, so I've altered it to report on step failures only rather than the success or failure of the job itself.

It's not something I would ever have had the time to investigate and implement from scratch, whereas now it's already deployed and in my Windows startup profile :-)

Many thanks!!!



cmallain
cmallain
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 38
this is how I formatted the time (using msdb.dbo.sysjobs though...). I am fairly new to more advanced SQL so hopefully I didn't waste too much time after seeing that above Crying

    ,convert(datetime,case
    when sjs.active_start_time = '0'
    then '00:00:00'

    when sjs.active_start_time < 1000
    then '00'+':0'+substring(cast(sjs.active_start_time as varchar(6)),1,1)+':00'

    when sjs.active_start_time < 10000
    then '00'+':'+substring(cast(sjs.active_start_time as varchar(6)),1,2)+':00'

    when sjs.active_start_time < 100000
    then '0'+
       substring(cast(sjs.active_start_time as varchar(6)),1,1)+':'+
       substring(cast(sjs.active_start_time as varchar(6)),2,2)+':'+
       substring(cast(sjs.active_start_time as varchar(6)),4,2)
    when sjs.active_start_time >= 100000
    then substring(cast(sjs.active_start_time as varchar(6)),1,2)+':'+
       substring(cast(sjs.active_start_time as varchar(6)),3,2)+':'+
       substring(cast(sjs.active_start_time as varchar(6)),5,2)

       else null
    end,108) as new_active_start_time


--select top 5 *
FROM msdb.dbo.sysjobs sj

left outer join msdb.dbo.sysjobschedules sjs
on sj.job_id = sjs.job_id
and sjs.enabled = 1
royce.bacon
royce.bacon
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 991
I'm really glad that many people are finding this useful. This is my first submission here and these kind of results makes me interested in submitting more.
TerryS
TerryS
SSC Eights!
SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)

Group: General Forum Members
Points: 956 Visits: 1422
Nice article. This appears to be a single server job monitoring tool. My need would be to do something similar for about 20 SQL servers scattered across 3 domains. Please correct me if I am wrong in my assumption. Thanks.



royce.bacon
royce.bacon
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 991
TerryS (12/15/2009)
Nice article. This appears to be a single server job monitoring tool. My need would be to do something similar for about 20 SQL servers scattered across 3 domains. Please correct me if I am wrong in my assumption. Thanks.


You are correct that it is single server. In my environment, a small shop, I only have two servers with 90+% of the jobs on one of them. And each server happens to have a Report Server on it (2000 vs. 2005). So What I've done is implement this report on each server. For 20 servers that may not be as practical. To do a combined one you might be able to do something with Linked servers and use a UNION of data from each server. I'm not real up on Linked Servers since like I said we don't have a real need for them, but it seems like that might be a route to try.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search