how to raise an error without job step failing ?

• VG-619426

  Hall of Fame

  Points: 3914

  More actions

  April 13, 2009 at 3:32 pm

  #208244

  HI,

  I have a job step, whick checks for record count and if it is equal to 0, then we have raiserror. IF not it goes to nest step and executes an exe.

  Step 1:

  DECLARE @Count int

  Select @Count = Count(ColID) from Table1

  WHERE ColFlag = 1

  if (@Count = 0)

  RAISERROR('No Active Request', 15,1)

  Step2 : Calls an exe.

  As I said, we want to execute step 2, only when there is a records in Table1 with the ColFlag1 = 1, other wise just quit the job.

  Any other alternative way of doing this ? I had set the On success: go to next step and on failure: quit the job reporting success. But still the job has a warning sign and the step failed with big red mark :-(.

  Thanks,

  VG

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736455

  More actions

  April 13, 2009 at 3:58 pm

  #976929

  I would add another statement to reset the errorlevel. a

  select 1

  ought to do it.

• VG-619426

  Hall of Fame

  Points: 3914

  More actions

  April 13, 2009 at 4:42 pm

  #976963

  Sorry, I didn't get it...Where to set the errorlevel ?

  Thanks Steve!!

• matt stockham

  SSCrazy Eights

  Points: 9942

  More actions

  April 13, 2009 at 7:56 pm

  #977000

  I would suggest as follows:

  DECLARE @Count int

  Select @Count = Count(ColID) from Table1 WHERE ColFlag = 1

  if (@Count > 0)

  Calls an exe.