August 17, 2012 at 7:33 am
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.
August 17, 2012 at 7:51 am
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.
August 17, 2012 at 8:04 am
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.
August 17, 2012 at 8:05 am
Reraise the error CATCH block. About the only way I can think of handling it.
August 17, 2012 at 8:16 am
Lynn Pettis (8/17/2012)
Reraise the error CATCH block. About the only way I can think of handling it.
Sorry, "Reraise" ?
August 17, 2012 at 8:20 am
He means use raiserror() in the catch block.
The probability of survival is inversely proportional to the angle of arrival.
August 17, 2012 at 8:29 am
sturner (8/17/2012)
He means use raiserror() in the catch block.
Ahhhh, thanks, I'll give that a go. 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply