For Experts: SQLAgent Jobs and Error Handling

For Experts: SQLAgent Jobs and Error Handling

Post by donn.. » Tue, 18 Apr 2000 04:00:00



Should I expect to be able to handle a "typical" error, such as primary
key violation, in a stored procedure so that a Job that runs that
procedure will not fail??

For example, I have the following procedure:

CREATE PROCEDURE aesp_ErrTest


AS





else


============

Note that I am "handling" / looking for error 2627, which is Primary
Key Violation.  If I call this procedure from ISQLW or from another
procedure, and pass in values that would cause error 2627, I would
still get a return code of 0 (despite the reporting of error 2627),
which is desired.  However, if I schedule a Job to run this procedure,
it reports the error and causes job failure.

I know that I can avoid a Primary Key Violation by checking for
existence before the insert.  But this would in essence cause double
work.  This simple kind of error handling in conjunction with Jobs is
not too much to ask for is it ??

Any insights you may have will be appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

For Experts: SQLAgent Jobs and Error Handling

Post by Tibor Karasz » Thu, 20 Apr 2000 04:00:00


Quote:>This simple kind of error handling in conjunction with Jobs is
> not too much to ask for is it ??

Bottom line:
I believe it is. If the executed TSQL statements raised one (or more) errors
with a severity level over 10, the job fails. Only workaround I can think of
is to hack sysmessages and lower the severity level (_not_ recommended).

One of reasons for above is that you cannot suppress errors at the TSQL
level.

One possibility is to use CmdExec tasks and execute OSQL.EXE with a script
file. I'd go for pre-checking in the code...
--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.


> Should I expect to be able to handle a "typical" error, such as primary
> key violation, in a stored procedure so that a Job that runs that
> procedure will not fail??

> For example, I have the following procedure:

> CREATE PROCEDURE aesp_ErrTest


> AS





> else


> ============

> Note that I am "handling" / looking for error 2627, which is Primary
> Key Violation.  If I call this procedure from ISQLW or from another
> procedure, and pass in values that would cause error 2627, I would
> still get a return code of 0 (despite the reporting of error 2627),
> which is desired.  However, if I schedule a Job to run this procedure,
> it reports the error and causes job failure.

> I know that I can avoid a Primary Key Violation by checking for
> existence before the insert.  But this would in essence cause double
> work.  This simple kind of error handling in conjunction with Jobs is
> not too much to ask for is it ??

> Any insights you may have will be appreciated.

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

1. Error in SQLAgent Job forces the job to fail

Any help will be appreciated:

MS SQL Server 2000, SP3, SQL Agent.

If an error ocured during job execution (some SQL statement fails), it
causes the whole job to fail.
Is there any way to prevent it?
I want to keep working because it is not fatal if some statement fails.

Marina

2. open a MS Access2000 database from VB6

3. SQLAgent error on job start

4. ODBC and password dialog

5. SQLAgent job does not start; no errors:

6. user interface

7. Error handling - DTS vs JOB

8. Alias and calculations

9. SQL 7.0sp3 Scheduled Jobs & Error Handling

10. Error handling in SQL server jobs

11. Error handling when running a SP in a SQL Server Job

12. SQL Agent Job / Error Handling

13. Error Handling / Job