Job step indicates success when if fact failed, why?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Job step indicates success when if fact...

Job step indicates success when if fact failed, why?

Rate Topic

Display Mode

Topic Options

Author

Message

raotor

Posted Friday, August 17, 2012 7:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, Visits: 239

Hello,

I have a job with 4 steps in that didn't produce the results I'd expected. I checked the job history log and could see that all steps had ticks next them them, so was puzzled as to why this should be when I can clearly see that the records that should have been written were not.

I figured out what I'd done wrong, but am concerned as to why the job continued on despite a failure to INSERT new rows into a table.

The message section of the job log for the step in questiin reads as follows:

Executed as user: NT AUTHORITY\SYSTEM. Error details [SQLSTATE 01000] (Message 0)  898 [SQLSTATE 01000] (Message 0).  The step succeeded.

Post #1346544

sturner

Posted Friday, August 17, 2012 7:51 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 1,315, Visits: 2,887

raotor (8/17/2012)

I figured out what I'd done wrong, but am concerned as to why the job continued on despite a failure to INSERT new rows into a table.

So, what was wrong with your code? Having a bug in your code doesn't always result in an error or exception that will make the job fail. Also, if you use try - catch you may be handling the errors yourself in some way.

The probability of survival is inversely proportional to the angle of arrival.

Post #1346551

raotor

Posted Friday, August 17, 2012 8:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 7:03 AM
Points: 158, Visits: 239

I had added a new NOT NULL column into a table which was being inserted into via a stored procedure in step 3 of the job. Since I had forgotten to update that stored procedure to include the new column within the INSERT statement, the thing bombed.

However, you are quite perceptive. I have indeed (after looking into the SP) used a BEGIN TRY .. CATCH block to catch any errors during the INSERT.

OK, do you know of a way to notify the job step that a failure has occurred so that I can have a job history that reflects any handled errors?

If not, will I need to remove the BEGIN TRY logic in order for this to happen.

Thanks.

Post #1346560