Trigger with SMS.

• Gavin Oosthuizen

  Old Hand

  Points: 365

  More actions

  December 12, 2006 at 10:52 pm

  #174051

  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

• faizjmc

  SSC Eights!

  Points: 881

  More actions

  December 13, 2006 at 12:26 am

  #677708

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

  Share it....

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  December 13, 2006 at 1:28 am

  #677714

  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

• Loner

  SSC-Insane

  Points: 21279

  More actions

  December 13, 2006 at 7:34 am

  #677764

  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?

• Gavin Oosthuizen

  Old Hand

  Points: 365

  More actions

  December 13, 2006 at 8:58 am

  #677808

  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