December 12, 2006 at 10:52 pm
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
December 13, 2006 at 12:26 am
I don hv any idea regarding ur ques, but can u post the snippet of the sms function that u hv..
Share it....
December 13, 2006 at 1:28 am
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
December 13, 2006 at 7:34 am
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?
December 13, 2006 at 8:58 am
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