March 6, 2012 at 7:42 am
I am trying to implement the sql job log information into a cdo.sys email notification if the job fails. I can successfully send emails based on success or failure of the job, but would like to append the job log info to the body of the email upon failure.
Here is how I am creating my email:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_SQLNotify] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[sp_SQLNotify]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) = " "
/*********************************************************************
This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '00.00.00.00'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
Then once I do my TSQL step, the next step is based upon success or failure
exec master.dbo.sp_SQLNotify 'sql@my.org','person1@my.org;person2@my.org','Backup Job Success : <servername> completed successfully'
or
exec master.dbo.sp_SQLNotify 'sql@my.org','person1@my.org;person2@my.org','Backup Job Failure : <servername> failed'
How could I force log output into @body only upon failure?
I found this article http://www.sqlservercentral.com/articles/SQL+Server+Agent/67726/, but unsure how to tie back into sql agent job. Would failure step look like this?
EXEC pr_GetStepFailureData 'myjob'
--return string to some variable @emsgString
exec master.dbo.sp_SQLNotify 'sql@my.org','person1@my.org;person2@my.org','Backup Job Failure : <servername> failed', ' + @emsgString + '
March 6, 2012 at 8:34 am
take a look here, this is the proc we use to send out any job failures
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply