stored procedure

• Best-330874

  SSCrazy

  Points: 2350

  More actions

  April 17, 2007 at 7:41 am

  #172073

  Hi,

     i need help on this.i have a stored procedure as follows which executes a job.i want to raiserror if the job fails.

  ALTER PROCEDURE [dbo].[Load_Audit_Excel]

                   (@Return_Code int Output,

     @Error_Flag int Output,

     @Message_Txt varchar(100) Output)

      

  AS

  BEGIN

   

    --interfering with SELECT statements.

   SET NOCOUNT ON;

    

      EXEC msdb.dbo.sp_start_job @job_name='Audit'

   

  Set @Return_Code = @@Error

  END

  Exit_Proc:

  Select @Error_Flag = Error, @Message_Txt = Message_Txt

  From ReturnCode

  Where Return_Code = @Return_Code

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 17, 2007 at 7:55 am

  #700911

  The stored procedure msdb.dbo.sp_start_job, starts the job and then returns.  If it returns 0, then the job was successfully started, else it failed to start.  If you want to know if the job itself failed, you need to either setup an alert to email or net send you  message if the job fails.

  

• Adam Angelini

  SSCrazy Eights

  Points: 8548

  More actions

  April 17, 2007 at 7:57 am

  #700912

  --grab the return code from start_job like this

  declare @return_code int

  EXEC @return_code = msdb.dbo.sp_start_job @job_name='Audit'

  if @return_code = 0 --SUCCESS

  IF @return_code = 1 --FAILURE

  --RAISE error here, see RAISERROR in BOL for more information on that