June 8, 2011 at 5:36 am
Hi
I have a stored procedure that loops though all the dbs on my server, via a cursor, and backs them up.
If the backup drive runs out of space, then the sp, when run through management studio or sqlcmd continues on to the next database.
If the same sp is run through SQL Agent, then it aborts at the first failure. Is there anyway of changing this behaviour so that it continues?
Thanks
Andy
June 8, 2011 at 6:10 am
Can you put error handling into the script so that it deals with the error and doesn't return it to SQL Agent?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2011 at 8:54 am
There is error handling, here's a code extract:
exec (@Command)
set @LocalError=@@error
-- If there's a problem record it in the event log
if not @LocalError = 0
begin
set @ErrorMessage= 'An Error'
if @ServerStatus = 1
raiserror(@ErrorMessage,16,1) with log
else
exec msdb.dbo.sp_send_dbmail @profile_name=@MailProfile,
@recipients = 'me@me.com',
@subject = 'Backup Failure',
@body = @ErrorMessage
end
--Get the next database to process
fetch CurDB into @Database
If there's an error, either put an entry in the event log or send an email, then get the next database
June 8, 2011 at 9:02 am
That's old style error handling. Try setting up a TRY/CATCH event.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 8, 2011 at 9:05 am
OK, will do. Code was originally written for SQL 2000!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply