September 19, 2007 at 9:17 am
Since you can only email one operator when a SQL Job fails I am trying to configure alerts to notify multiple operators.
Basically, I ran the following to create a new error
sp_addmessage 50001, 16, N'Job Failed.'
I then configured a new alert and specified error number 50001, and selected my operator account in the response screen.
I then ran
RAISERROR (50001, 16, 1)
I get the message:
Msg 50001, Level 16, State 1, Line 1
Job Failed.
However, it is not triggering my alert. When I go to the history screen of my alert is shows the number of occurrences to be 0. Does anyone have any ideas on what I'm doing wrong?
September 20, 2007 at 7:48 am
My technique (SQL Server 2005) to capture all alerts using SQL Tokens and emailing the message.
Configure the alert and on Response select "Execute a job"..select the Raise Alert Error job.
Create a new job: called "Raise Alert Error". Make this the first step of the job.
exec SendSQLAlert N'$(ESCAPE_SQUOTE(SRVR))', N'$(ESCAPE_SQUOTE(A-ERR))', N'$(ESCAPE_SQUOTE(A-MSG))' , N'$(ESCAPE_SQUOTE(MACH))', N'$(ESCAPE_SQUOTE(A-DBN))'
create a stored procedure to send an email. Modify the stored procedure to use smtp mail or sqlmail. This format is for SQL Server 2005.
Create
proc [dbo].[SendSQLAlert] (
@Server as varchar(20),
@Error
as varchar(50),@Message as varchar(1000),
as varchar(100),@DBName as varchar(100)
)
as
declare
@Subject varchar(500)
declare
@Body varchar(1000)
Set
@Body = @Error + '
'
+ @Message + '
'
+ @Machine
Select
@Subject = @Server + ' ' + @DBName
EXEC
msdb.dbo.sp_send_dbmail
@Profile_Name
= 'whateverl',
@recipients
= 'whoever@here.com; whoever2@here.com',
@Subject
= @Subject,
@Body
= @Body
September 20, 2007 at 7:52 am
After reading your message again, I realize I didn't answer it. Am not going to delete my post, but I am going to get your answer.
September 20, 2007 at 8:11 am
see if this helps. This code looks at a queue table for records that have been sitting in queue longer than 5 minutes. If it finds a record, it raises an error and sends emails.
Step1
Declare @nRet int
Select @nRet= count(*)
from table (nolock)
where
DATEDIFF(minute, lastmodified, getdate()) > 5
begin
If @nRet > 0
RAISERROR ('System has halted. Please investigate ASAP.', 16, 1)
end
(this steps is in response to: If the job fails)
Email Failure
exec master.dbo.xp_smtp_sendmail
@FROM = N'SQL.mail@test.com',
@FROM_NAME ="[SRVR]" ,
@TO = N'john.doe@email.com, jane.smith@email.com',
@priority = N'High',
@message = 'Paging has Halted. Please Investigate ASAP',
@server = N'Relay.email.Net',
@Subject = 'Paging has Halted. Please Investigate ASAP.'
Raise SNMP Trap (CmdExec)
opcmsg sev=critical app="OS" obj=sql msg_text="Paging has Halted. Please Investigate ASAP"
September 20, 2007 at 8:50 am
I believe what I'm trying to do should be completely supported. Basically I'll have steps in a job. If any of the steps fail they will go to a step that will RAISERROR and then the job will end. The configured alerts are supposed to catch that that error was raised and email the operators that are configured. I posted my steps in my original post, the error appears to be raised correctly, however the alert is not being triggered when that error is raised.
September 20, 2007 at 8:55 am
Try using the "with log" option when you raise the error, e.g. "RAISERROR (50001, 16, 1) WITH LOG". I don't remember exactly why it works, but this seems to be necessary for triggering alerts (something about the alert only "seeing" the error when it's written to the log).
--MIJ
September 20, 2007 at 9:09 am
That did the trick. Thank you so much, it's been frustrating the heck out of me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply