Trigger with SMS.

  • Good day,

    I have been tasked with developing stored proc that will SMS the DBA's when a job fails. This obviously will only be sent when a job fails, with the relevant information (date, time, job name).

    I pretty much have the script for the sms function, but how should i go about querying the sys processes/sys jobs at regular intervals? Trigger???

    Any help or ideas would be appreciated.

    Take care,

    Gavin

  • I don hv any idea regarding ur ques, but can u post the snippet of the sms function that u hv..

    Share it....

  • For job to fail a step inside this job must fail.

    Create a new step for sending SMS.

    Set this step to be executed only on another step failure.

    _____________
    Code for TallyGenerator

  • If you create a job, then in each step, in the 'Advance' you can specify what to do when the step fails.

    In the job itself, there is a 'Notification' tab, you can email, page, net send to anyone when the job fails.

    Why do you need a trigger?

  • This is what i have so far. The @emailist is configured with the cellphone/mail account through your service provider. The script works fine, but i initially thought i would need the use of a trigger, but as you said, i am rather going to put a step in each job, if fails run query, kind of thing. I was originally going to query the sysjobhistory table, but this would probably end in too many records being returned at once, instead of getting a specific job failure.

    declare @jobname varchar (30)

    select @jobname = [name]

    from msdb..sysjobs a inner join msdb..sysjobhistory b on

    a.job_id = b.job_id

    where run_status = 0

    Begin

    Declare @Subject varchar(100)

    Declare @Text varchar(1000)

    Declare @EmailList varchar(1000)

    Declare @CopyEmail varchar(1000)

    Declare @blind_copy_recipients varchar(1000)

    Set @Subject = 'job failed'

    Set @Text = 'Job failed' + '' + '' + @jobname

    Set @EmailList = 'cellphone'

    Set @CopyEmail = 'your@mail.com'

    /*Set @blind_copy_recipients =

    */

    --declare @jobname varchar(100)

    --declare @spid smallint

    declare dCur cursor for

    (

    select distinct [name]

    from msdb..sysjobs a inner join msdb..sysjobhistory b on

    a.job_id = b.job_id

    where run_status = 0

    )

    open dCur

    fetch next from dCur into @jobname

    close dcur

    deallocate dcur

    exec master..xp_sendmail @CopyEmail, @text,'', '',

    @blind_copy_recipients, @EmailList, @Subject, null, null, null, null, @width = 1000

    end

    Thanks for th replies, chat later.

    Gavin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply