Does anyone know why a backup database statement aborts a job in a stored procedure so that you can't handle the error with @.@.error, but when you run the same stored procedure in a query window the error handling does get run?
The snippet of code looks like this:
SET XACT_ABORT OFF
SET ANSI_NULLS OFF
set ansi_warnings off
set arithabort on
declare @.backup_status int
declare @.cmd varchar(1024)
SET NOCOUNT ON
print 'Options value: ' + convert(varchar, @.@.options)
set @.backup_status = 0
if (@.backup_mode = 'F')
begin
BACKUP DATABASE
@.DB TO DISK = @.BackupFile
with
NAME = @.Name,
DESCRIPTION = @.Description,
MEDIANAME = @.MediaName,
MEDIADESCRIPTION = @.Description,
STATS = 10,
init, nounload, noskip
if @.@.error != 0
begin
set @.backup_status = 1
print '**************** Backup of ' + @.DB + ' Failed'
end end
When this is run in a query window, the print statement at the bottom does get executed, but when run as a step in a sql agent job it does not.
Thanks.
As an additional comment, this doesn't seem to happen on my SQL 2000 server, just the SQL 2005 sp1 server.