Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Job Status Report with Error Detail Expand / Collapse
Author
Message
Posted Tuesday, December 15, 2009 8:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:52 AM
Points: 42, Visits: 172
makes sense :) thanks for the reply.
Post #834524
Posted Tuesday, December 15, 2009 8:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #834540
Posted Tuesday, December 15, 2009 8:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 17, 2014 12:34 PM
Points: 1,414, Visits: 4,539
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #834543
Posted Tuesday, December 15, 2009 8:30 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 21, 2010 7:28 AM
Points: 21, Visits: 79
A shared datasource with SA credentials on a report server.... what's the worst that could happen? ;)

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
Post #834556
Posted Tuesday, December 15, 2009 8:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 37, Visits: 738
Gator-221056 (12/15/2009)
A shared datasource with SA credentials on a report server.... what's the worst that could happen? ;)

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.
Post #834564
Posted Tuesday, December 15, 2009 9:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:38 AM
Points: 558, Visits: 545
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!!!



Post #834603
Posted Tuesday, December 15, 2009 10:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 5, 2012 10:10 PM
Points: 6, 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

,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

Post #834651
Posted Tuesday, December 15, 2009 11:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 37, Visits: 738
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.
Post #834654
Posted Tuesday, December 15, 2009 12:41 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 5:27 AM
Points: 879, Visits: 987
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.


Post #834714
Posted Tuesday, December 15, 2009 12:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 37, Visits: 738
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.
Post #834721
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse