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 «««1234»»

SCOME - Part 3: The Failed Jobs Report Expand / Collapse
Author
Message
Posted Monday, May 11, 2009 7:39 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 12, 2014 9:26 PM
Points: 68, Visits: 172
Hi Gary,

I'm guessing that the reason this is happening is because one of your jobs has never been run, or under last_run_time/date there is something other than dates such as "Unknown"? I'll modify the script to handle this, but in the mean time, just give that job a dummy run date. The problem will only appear the first time you create a job and it has never been run. You can give it a dummy run either by doing so in the system table or by telling SSMS that the job should report Success on Failure. Then run it, then change it back to Success on Succeed. This can be done in the advanced tab of the job.
Post #714106
Posted Monday, May 11, 2009 7:40 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 12, 2014 9:26 PM
Points: 68, Visits: 172
I have sent article part 4 to SQLServerCentral. It may be weeks before it is published though, so I have also published it at thebuddingdba.com. This part is an indepth but very concise introduction to Visual Studio, .Net and how to display the FailedJobs report in a browser. It can be accessed here.
Post #714109
Posted Tuesday, May 12, 2009 7:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 15, 2013 9:20 AM
Points: 18, Visits: 113
Thanks Drew, I think I'll have to wait for your code change, as I can't change the users jobs on the servers. Please let me know when you've made the code change.

Thanks!

Gary
Post #715018
Posted Tuesday, May 12, 2009 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 15, 2013 9:20 AM
Points: 18, Visits: 113
I was able to create my web page! Thanks Drew!

Gary
Post #715019
Posted Tuesday, May 12, 2009 7:56 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 12, 2014 9:26 PM
Points: 68, Visits: 172
gary.dunn (5/12/2009)
I was able to create my web page! Thanks Drew!

Gary


Great!
Post #715077
Posted Tuesday, May 12, 2009 2:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 15, 2013 9:20 AM
Points: 18, Visits: 113
Drew, I've added a bunch of servers and except for convert issue, it's going great....except for one server I'm getting this error when I run usp_getfailedjob:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'originating_server'.

Any idea why this one server has this error and the others are fine?

Thanks,

Gary
Post #715456
Posted Tuesday, May 12, 2009 2:25 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, January 12, 2014 9:26 PM
Points: 68, Visits: 172
gary.dunn (5/12/2009)
Drew, I've added a bunch of servers and except for convert issue, it's going great....except for one server I'm getting this error when I run usp_getfailedjob:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'originating_server'.

Any idea why this one server has this error and the others are fine?

Thanks,

Gary


What SQL Server version and edition is it? It seems that SQL Server cannot find this column in one of the two system tables. Do you permission on your server to browse the system table columns?
Post #715461
Posted Tuesday, May 12, 2009 2:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 15, 2013 9:20 AM
Points: 18, Visits: 113
My mistake, the SQL version is 2005 but I specified in the server_sql_details table that it is a 2000. I changed the value from 8 to 9 and it now works....thanks!
Post #715478
Posted Wednesday, August 26, 2009 3:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 12:11 PM
Points: 8, Visits: 250
Drew - Great script!

However, similar to Gary I am also getting the conversion error:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.



When I bracket and quote the named instance like: ['SERVERNAME\INSTANCENAME'] the SP runs successfully but no failures appear in the table. The weird thing is this works perfectly on my test server but not prod, both are 2005 with no differences that I am aware of.

Any ideas?

BTW, the job that failed is not a new job, has been run daily for a few months now. Thanks!!
Post #777907
Posted Thursday, August 27, 2009 3:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 19, 2013 12:11 PM
Points: 8, Visits: 250
Drew,

Never mind on this. I just ran a select statement using a convert statement for the datetime and for whatever reason, still got the error. I rewrote this job with some other scripts I previously wrote and was able to get something very similar to what you wrote here on the site.

Anyway, keep up the good work. Your scripts and articles definitely get me going in a direction to improve database processes for my company. Thanks for helping us think outside the box!
Post #778782
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse