SQL Server Performance Forum – Threads Archive

Send email (/run sp) on job failure

I thaught I would ease my lazy self off the "hassle" of going through the job history every day and try to get sql-server to send me an email if a job I have set up fails instead. I have read that I would need a mapi-profile to use the "Email-operator notification" in the job-setup and I really don’t know how to set that up so I thaught of using a nice little sp I found on sqlteam.com http://www.sqlteam.com/item.asp?ItemID=5003) instead. But how would I go about setting up the agent to run this procedure only if a job fails? Is it at all possible…? —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

Hi ya, You can add a job step to the job and set the previous step to complete with success if it succeeds and to go to the next step if it fails Cheers
Twan

There you go <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> I almost feel stupid for not thinking about this…thanx!<br /><br />–<br />Frettmaestro<br />"Real programmers don’t document, if it was hard to write it should be hard to understand"

Frettmaestro, I guess I need to install ASPMail before I could use
SMTPsvg package, right? It is not free. – mingus

Well, in order to use the ASPmail-component you would need to purchase it but I have always used the free ASPEmail instead and I’m quite sure that the procedure will work for just about any object like this… ->http://www.aspemail.com —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

thanks but when I downloaded aspemail.exe, I got only aspemail.dll,
aspupload.dll, emaillogger.dll, emailagent.exe,a nd a cpl file.
There is no SMTPsvg.dll.

In the demoscript provided from sqlteam the object reference is "SMTPsvg.Mailer" while the object reference for aspemail is "Persits.MailSender" and all the property names are different. I never got around to implement this but I’m actually looking at it right now. If I get it to work with aspemail I’ll post the sp here… —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

Allright, I found some stuff in BOL that I modified and I got it to work perfectly with the free ASPEmail from Persists:
ALTER Procedure sp_SMTPMail
@ToAddress varchar(100),
@FromAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000)
AS
SET NOCOUNT ON
DECLARE
@object int,
@hr int,
@property varchar(255),
@return varchar(255),
@src varchar(255),
@desc varchar(255),
@Mailserver varchar(100) SET @Mailserver = ‘smtp.mymailserver.com’ — First, create the object.
EXEC @hr = sp_OACreate ‘Persits.MailSender’, @object OUT
IF @hr <> 0
BEGIN
— Report any errors
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), [email protected], [email protected]
GOTO END_ROUTINE
END
ELSE
— An object is successfully created.
BEGIN
— Set properties
EXEC @hr = sp_OASetProperty @object, ‘Host’, @Mailserver
IF @hr <> 0 GOTO CLEANUP
EXEC @hr = sp_OASetProperty @object, ‘From’, @FromAddress
IF @hr <> 0 GOTO CLEANUP
EXEC @hr = sp_OASetProperty @object, ‘Subject’, @Subject
IF @hr <> 0 GOTO CLEANUP
EXEC @hr = sp_OASetProperty @object, ‘Body’, @Body
IF @hr <> 0 GOTO CLEANUP EXEC @hr = sp_OAMethod @object, ‘AddAddress’, NULL, @ToAddress
IF @hr <> 0 GOTO CLEANUP EXEC @hr = sp_OAMethod @object, ‘Send’, NULL
GOTO CLEANUP END CLEANUP:
— Check whether an error occurred.
IF @hr <> 0
BEGIN
— Report the error.
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), [email protected], [email protected]
END — Destroy the object.
BEGIN
EXEC @hr = sp_OADestroy @object
— Check if an error occurred.
IF @hr <> 0
BEGIN
— Report the error.
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), [email protected], [email protected]
END
END
END_ROUTINE:
RETURN
This should as far as I know be plug’n’play… —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

Great, frettmaestro! I just used this tool to send an email to my boss with
@fromaddress = myboss’[email protected]
QUESTIONs: (1) is this free aspMail really free, I meant it
is not like free for 30 days, right? (2) I guess the security feature is not free. -mingus

frettmaestro, the point I was trying to make in the prior msg is:
is there anyway we could limit the @fromaddress is
the actual one logged on to the server sql domain
account, instead of just anything? Thanks. -mingus

]]>