How To Explicitly Fail Job Step? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How To Explicitly Fail Job Step?

Author

Topic

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 2005-08-31 : 13:58:19

I have a job with 8 steps. The first step checks for rows in a table and if none exist should fail the step so no other steps are executed. The way the developer gets the step to fail is by dividing by 0. This can't be the best way to implement this. What's the best way to explicitly fail the step? Here's the SQL used for step 1:

select top 1 * from tableA

if @@rowcount=0
begin
declare @forceerror int
--Force Divide by Zero Error to stop job
set @forceerror =0/0
end

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-31 : 14:15:00

Raiserror will with specific severity levels will force a job step to fail. That is what the devide by 0 was doing. Why not this:


if @@rowcount = 0 
begin
      Raiserror('No records in tableA. Abort JobStep', 17, 1)
end

EDIT:
Check raiserror and severity levels in books Online for details

Be One with the Optimizer
TG

peterlemonjello
Yak Posting Veteran

53 Posts

Posted - 2005-08-31 : 14:26:54

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources