SCOME - Part 3: The Failed Jobs Report

Question

SCOME - Part 3: The Failed Jobs Report

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

gary.dunn Old Hand Points: 382 More actions · Answer 1

Hi Drew, no we haven't got this working. We'll be adding more named instances to this server, so SCOME would really be a great tool to monitor them. Just need to be able to connect to them. Do you have any ideas?

Thanks,

Gary

Nick Hart SSC Veteran Points: 279 More actions · Answer 2

Hey Drew, nice set of articles. Have something similar at work and monitor 80+ instances and that's just MSSQL. Though we only use Rep Services to display results. The .Net is what we're thinking might be really good for us.

Gary - for named instances we have the linked server as "SERVERNAME\INSTANCENAME" and then in our servers table we have the servername as that SERVERNAME\INSTANCENAME. This wroks a treat for us. On one of our development boxes has 3 2000 and 3 2005 named instances. Can get stat's for all of them. Hope this help

Nick

gary.dunn Old Hand Points: 382 More actions · Answer 3

Hi Nick, I thought we tried that and it didn't work. I tried it again and now it works?! I must've been doing something wrong. Next question: were you able to get the usp_GetFailedJob stored procedure to work? Here's what I did...I failed a job on my named instance SERVER1\TSTINST1. I also failed a job on a server with just the default instance called SERVER2. I added both servers to the Server_SQL_Details table. I execute usp_GetFailedJob with my named instance server as the parameter (like this - exec usp_GetFailedJob SERVER1\TSTINST1 and it gives an error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '\'.

It doesn't like the \. How did you get around that?

Thanks!

Drew Salem Ten Centuries Points: 1060 More actions · Answer 4

gary.dunn (5/7/2009)
Hi Nick, I thought we tried that and it didn't work. I tried it again and now it works?! I must've been doing something wrong. Next question: were you able to get the usp_GetFailedJob stored procedure to work? Here's what I did...I failed a job on my named instance SERVER1\TSTINST1. I also failed a job on a server with just the default instance called SERVER2. I added both servers to the Server_SQL_Details table. I execute usp_GetFailedJob with my named instance server as the parameter (like this - exec usp_GetFailedJob SERVER1\TSTINST1 and it gives an error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'.
It doesn't like the \. How did you get around that?
Thanks!

Have you tried it with brackets? Exec usp_GetFailedJob [SERVER1\TSTINST1]

gary.dunn Old Hand Points: 382 More actions · Answer 5

That did the trick! Thanks! I've added additional servers to table Server_SQL_Details for a total of 10. I have one job in failed state on my named instance server.

So I run it with the brackets and I check the Failed_Jobs table and there is one entry, which is good.

Then I run usp_Iterate_FailedJobs procedure and I get this:

(10 row(s) affected)

(1 row(s) affected)

Msg 241, Level 16, State 1, Line 3

Conversion failed when converting datetime from character string.

I check table Failed_Jobs and it is empty. Am I doing something wrong?

Thanks,

Gary

Drew Salem Ten Centuries Points: 1060 More actions · Answer 6

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.

Drew Salem Ten Centuries Points: 1060 More actions · Answer 7

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.

gary.dunn Old Hand Points: 382 More actions · Answer 8

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

gary.dunn Old Hand Points: 382 More actions · Answer 9

I was able to create my web page! Thanks Drew!

Gary

Drew Salem Ten Centuries Points: 1060 More actions · Answer 10

gary.dunn (5/12/2009)
I was able to create my web page! Thanks Drew!
Gary

Great!

gary.dunn Old Hand Points: 382 More actions · Answer 11

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

Drew Salem Ten Centuries Points: 1060 More actions · Answer 12

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?

gary.dunn Old Hand Points: 382 More actions · Answer 13

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!

DBA_Oshvegas SSC Enthusiast Points: 126 More actions · Answer 14

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!!

DBA_Oshvegas SSC Enthusiast Points: 126 More actions · Answer 15

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!