Help retrieve errors messages

[mercwrought]

Ok I have a huge problem now. I need to retrieve all errors and messages from a job that ran and failed. In viewing it in job history the small description box cuts off the message in it.

I don’t have sp_help_job…. anything and I’m not sure that they would have helped me anyway. I’m looking for where the errors are stored and how to get to them.

Thx for any help

 

[NoCoolHandle]

I am guessing sysjobhistory in the MSDB database...

 

[mercwrought]

Ok I take it all back I have found the sp and the tables but the information in the message column is limited to 1024 charters is there any where I can view the rest of the message?

 

[NoCoolHandle]

Have you tried turning on the profiler and then running the job...

 

[mercwrought]

Well the problem with that the job is a merge procedure and I am just combining random accounts on a test server right now. The accounts are gone and I would have to restore the db and merge the 300+ accounts before I get to this set, it would be a pain and time consuming. I was hoping to be able to read the messages out of a table.

This brings me to my new thought. I know that @@ERROR returns an error message number which can be compared to sysmessages for a template, but can I get the message with the variables in it. Can I get the row number of where it occurred?

What I plan to do is get the error and the row it occurs in and if there is an error in my procedure store it in a table for my review.

 

[NoCoolHandle]

If you know what the error was (errnum or partial message), is it possible to just trap it with an alert and email it back to yourself?

 

[mercwrought]

Ok I have set up an alert. I am using net sent instead of e-mail (just because I don’t want to set up the e-mail right now). I have yet had it send me an error message and I have found one of the problems. However I am still interested on error messages from my program so any help would be welcome.

Thx NoCoolHandle for you input thus far.