ScheduledJob Aborts StoredProc when Error Detected

Question

ScheduledJob Aborts StoredProc when Error Detected

Queen

Valued Member

Points: 61
More actions
April 7, 2005 at 6:03 pm

#185709

I have a stored proc that backups up several databases. When I run this storedproce from query analyzer it detects an error upon backing up a database it continues processing the backup of the remaining databases and finishes the executing.
However when I schedule this storedproc in a job, the job detects an error and does not process the rest of the stored proc and aborts. Meaning the remaining databases don't get backed up. Is there any way to have the scheduled job ingnore the raised error in the stored proc and go on processing the rest of the stored procedure?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ronald Burgmeijer SSC Journeyman Points: 85 More actions · Answer 1

You probably need to change a setting in the job.

There is a 'On failure action'. The standard setting is 'Quit the job reporting failure'. Change this to 'Goto the next step'.

Also make sure that each backup job has it's own step, otherwise this won't work.

Cheers,

Ronald

Queen Valued Member Points: 61 More actions · Answer 2

Thanks for your input, but let me give you more information because I don't want to hard code every step.

My storedproc opens a cursor and then query's the the sysdatabases for all databases that are not tempdb,model,etc. into that cursor.

Then it fetches each item in the cursor into a variable. While there is no fetch error it populates another variable with other stuff and does a concatenation.

Then it executes the backup database comand with info from the variables as well as other info. Fetches the next item in the cursor.

So, there is not one line for each backup statement, rather there is one backup statement with variables that are cycled through. The problem is that when I run this code in query analyzer as soon as it hits a database that can not be backed up, it raises and error, but moves on to backing up the next database. When I schedule the same stored proc. as soon as it hits a databast that can not be backed up it ABORTS and does not go on to backup the rest of the databases. So, WHY the difference between Query Analyzer and a scheduled Job?????

Thanks!!!

Ronald Burgmeijer SSC Journeyman Points: 85 More actions · Answer 3

The difference should be in the error handling. I'm sure about this but I think that the scheduled job uses it's own error handling as well which would be why the job would fail before the code has run entirely.

Perhaps someone else knows if this is so and if so, why it is so.

Cheers,

R