Prev: Fueling your car with natural gas from home
Next: Using a 'Union' kills the performance
From: Jimbo on 22 Jun 2010 10:49
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.

From: Erland Sommarskog on 22 Jun 2010 14:12
Jimbo (james.goodwill(a)googlemail.com) writes:
> 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.

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(a)sommarskog.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/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Fueling your car with natural gas from home
Next: Using a 'Union' kills the performance