<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Royce Bacon
»
Job Status Report with Error Detail
28 posts, Page 2 of 3
««
1
2
3
»»
Job Status Report with Error Detail
Rate Topic
Display Mode
Topic Options
Author
Message
l543123
l543123
Posted Tuesday, December 15, 2009 8:00 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, September 28, 2012 6:27 AM
Points: 42,
Visits: 170
makes sense :) thanks for the reply.
Post #834524
alen teplitsky
alen teplitsky
Posted Tuesday, December 15, 2009 8:13 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, June 10, 2013 10:43 AM
Points: 1,411,
Visits: 4,517
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
alen teplitsky
alen teplitsky
Posted Tuesday, December 15, 2009 8:16 AM
Ten Centuries
Group: General Forum Members
Last Login: Monday, June 10, 2013 10:43 AM
Points: 1,411,
Visits: 4,517
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
Gator650
Gator650
Posted Tuesday, December 15, 2009 8:30 AM
Grasshopper
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
royce.bacon
royce.bacon
Posted Tuesday, December 15, 2009 8:40 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 7:33 AM
Points: 34,
Visits: 647
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
PSB1
PSB1
Posted Tuesday, December 15, 2009 9:43 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Yesterday @ 4:48 AM
Points: 555,
Visits: 494
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
cmallain
cmallain
Posted Tuesday, December 15, 2009 10:56 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, April 05, 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
royce.bacon
royce.bacon
Posted Tuesday, December 15, 2009 11:06 AM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 7:33 AM
Points: 34,
Visits: 647
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
TerryS
TerryS
Posted Tuesday, December 15, 2009 12:41 PM
SSC Eights!
Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 842,
Visits: 774
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
royce.bacon
royce.bacon
Posted Tuesday, December 15, 2009 12:57 PM
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, June 12, 2013 7:33 AM
Points: 34,
Visits: 647
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 »
28 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
