SQL Server Performance

sql server Job issues

Discussion in 'SQL Server 2005 Integration Services' started by ashish.johri, Oct 8, 2007.

  1. ashish.johri New Member

    I have two questions for the experts:1. There are 2 steps in one job. Even if one is succeed then EXECUTE @IRetval = msdb.dbo.sp_get_composite_job_info @schedule_id = 8 IF (@IRetval <> 0) BEGIN SET @BResult = 0 --FailureEND ELSE BEGINSET @BResult = 1 --SuccessEND gives success. Do I need to create two jobs if I need to address whether all the steps are successful?2. If the job fails then to which table does error code and error description is populated?Regards,Ashish
  2. satya Moderator

    It is not clear what you want to achieve, can you explain what you what with this code.
  3. ashish.johri New Member

    1. There are 2 steps in one job. Even if one is succeed then
    I execute the below code to get whether the job is successful or not. Schedule Id for job is 8.
    EXECUTE @IRetval = msdb.dbo.sp_get_composite_job_info @schedule_id = 8
    IF (@IRetval <> 0)
    BEGIN SET @BResult = 0 --Failure
    END
    ELSE
    BEGIN SET @BResult = 1 --Success
    END
    If the first step is failed and the second is success the code (as above) reports success.
    Do I need to create two jobs if I need to address whether all the steps are successful?

    2. If the job fails then to which table does error code and error description is populated?
    Regards,
    Ashish
  4. satya Moderator

    Ok to control the error you could do with 2 jobs, where the second job can be executed using SP_START_JOB procedure.

Share This Page