SQL2000 SP4.

I have a SQL Store Procedure that performs a database backup, to a
disk file, for each 'user' database in the SQL instance. The script is
simple; it just uses a 'fetch' loop and performs a 'BACKUP
DATABASE...' for each database.

If I run the script in QA, and one of the database backups fails for
any reason - backup disk full, path not found etc., the script just
loops through to the next database. Which is what I want.

However, if I then run the SP as a scheduled job, the job fails, and
does not continue to the next database backup.

How can I get the SP to ignore the BACKUP DATABASE failure(s) when
it's executed from a scheduled job? I guess these are O/S errors being
passed back to SQL.

Thanks very much. James.

Jimbo (james.goodwill (AT) googlemail (DOT) com) writes:
In SQL 2000, you cannot suppress errors. The problem is that Agent aborts
the job on errors.

I think the easiest way out is to run the job as a CmdExec job, and
in this job you invoke OSQL to run the procedure.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx