Sunday, March 11, 2012

Backup database aborts job in the agent, but not when in a query window

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.

No comments:

Post a Comment