Using a trigger on sysjobhistory to send mail via cdosysmail

I am trying to use the code below to send messages if a job fails. It doesn't
work, am I doing something wrong.. help needed.

Thanks.

USE [msdb]

GO

/****** Object: Trigger [dbo].[trg_stepfailures] Script Date: 05/09/2008
23:01:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[trg_stepfailures]

ON [dbo].[sysjobhistory]

FOR INSERT

AS

DECLARE @strMsg varchar(4000)

DECLARE @Attachment varchar(4000)

IF EXISTS (SELECT * FROM inserted WHERE run_status IN (0,3) AND step_name <>
'(job outcome)')

BEGIN

SELECT @strMsg =

convert(char(15),'Server:') + isnull(@@servername, '') +

char(10) +

convert(char(15),'Job:') + isnull(convert(varchar(50), sysjobs.name), '') +

char(10) +

convert(char(15),'Step:') + isnull(convert(varchar(50), inserted.step_name),
'')+

char(10) +

convert(char(15),'Action:') + CASE run_status WHEN 0 THEN 'FAILED' WHEN 3
THEN 'CANCELED' ELSE '' END+

char(10) +

convert(char(15),'Message:') + isnull(convert(varchar(150),
inserted.message), '')+

char(10) +

convert(char(15),'Attachment:') + isnull(convert(varchar(150),
sysjobsteps.output_file_name),''),

@Attachment = sysjobsteps.output_file_name

FROM inserted

JOIN sysjobs

ON inserted.job_id = sysjobs.job_id

JOIN sysjobsteps

ON inserted.job_id = sysjobsteps.job_id

AND inserted.step_id = sysjobsteps.step_id

WHERE inserted.run_status IN (0,3)


DECLARE @Loop int

DECLARE @EmailTo varchar(1000)

DECLARE @EmailFROM varchar(1000)

DECLARE @Subject varchar(100)

SELECT @Subject = 'Job Failure'

SELECT @EmailFROM = 'Kwame.Adu-Poku@va.gov'

SELECT @EmailTo = 'Kwame.Adu-Poku@va.gov'

EXEC master.dbo.sp_Send_CDOSysMail @EmailFROM, @EmailTo, @Subject, @strMsg,
@Attachment

END



GO

USE [master]

GO

/****** Object: StoredProcedure [dbo].[usp_Send_CDOSysMail] Script Date:
05/09/2008 23:01:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_Send_CDOSysMail]

@From varchar(4000),

@To varchar(4000),

@Subject varchar(4000)=" ",

@Body varchar(4000) =" ",

@Attachment varchar(4000) = " "

/*********************************************************************

This stored procedure takes the parameters and sends an e-mail.

All the mail configurations are hard-coded in the stored procedure.

Comments are added to the stored procedure where necessary.

References to the CDOSYS objects are at the following MSDN Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosy...tml/_cd

***********************************************************************/

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.

--
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosy...tml/_cd

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', 'smtp.va.gov'

-- 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

-- Check for Attachment specified and attach if necessary.

IF @Attachment IS NOT NULL

BEGIN

EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', null, @Attachment

-- EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @ATTACHMENT --@rv captures
the method return and recieved emails with attachments without a problem.

END

-- 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 have to.

-- Clean up the objects created.

EXEC @hr = sp_OADestroy @iMsg



--
KPoku

Why don't you do this using Enterprise Manager \ SSMS?

For example, if it's SQL Server 2005 just go to a job's properties ->
Notifications. Database Mail must be configured first and then appropriate
Operators must be created.

--
Ekrem Önsoy




"opokad06" wrote in message

>I am trying to use the code below to send messages if a job fails. It
>doesn't
> work, am I doing something wrong.. help needed.
>
> Thanks.
>
> USE [msdb]
>
> GO
>
> /****** Object: Trigger [dbo].[trg_stepfailures] Script Date: 05/09/2008
> 23:01:09 ******/
>
> SET ANSI_NULLS ON
>
> GO
>
> SET QUOTED_IDENTIFIER ON
>
> GO
>
> CREATE TRIGGER [dbo].[trg_stepfailures]
>
> ON [dbo].[sysjobhistory]
>
> FOR INSERT
>
> AS
>
> DECLARE @strMsg varchar(4000)
>
> DECLARE @Attachment varchar(4000)
>
> IF EXISTS (SELECT * FROM inserted WHERE run_status IN (0,3) AND step_name
> <>
> '(job outcome)')
>
> BEGIN
>
> SELECT @strMsg =
>
> convert(char(15),'Server:') + isnull(@@servername, '') +
>
> char(10) +
>
> convert(char(15),'Job:') + isnull(convert(varchar(50), sysjobs.name), '')
> +
>
> char(10) +
>
> convert(char(15),'Step:') + isnull(convert(varchar(50),
> inserted.step_name),
> '')+
>
> char(10) +
>
> convert(char(15),'Action:') + CASE run_status WHEN 0 THEN 'FAILED' WHEN 3
> THEN 'CANCELED' ELSE '' END+
>
> char(10) +
>
> convert(char(15),'Message:') + isnull(convert(varchar(150),
> inserted.message), '')+
>
> char(10) +
>
> convert(char(15),'Attachment:') + isnull(convert(varchar(150),
> sysjobsteps.output_file_name),''),
>
> @Attachment = sysjobsteps.output_file_name
>
> FROM inserted
>
> JOIN sysjobs
>
> ON inserted.job_id = sysjobs.job_id
>
> JOIN sysjobsteps
>
> ON inserted.job_id = sysjobsteps.job_id
>
> AND inserted.step_id = sysjobsteps.step_id
>
> WHERE inserted.run_status IN (0,3)
>
>
> DECLARE @Loop int
>
> DECLARE @EmailTo varchar(1000)
>
> DECLARE @EmailFROM varchar(1000)
>
> DECLARE @Subject varchar(100)
>
> SELECT @Subject = 'Job Failure'
>
> SELECT @EmailFROM = 'Kwame.Adu-Poku@va.gov'
>
> SELECT @EmailTo = 'Kwame.Adu-Poku@va.gov'
>
> EXEC master.dbo.sp_Send_CDOSysMail @EmailFROM, @EmailTo, @Subject,
> @strMsg,
> @Attachment
>
> END
>
>
>
> GO
>
> USE [master]
>
> GO
>
> /****** Object: StoredProcedure [dbo].[usp_Send_CDOSysMail] Script Date:
> 05/09/2008 23:01:58 ******/
>
> SET ANSI_NULLS ON
>
> GO
>
> SET QUOTED_IDENTIFIER ON
>
> GO
>
> CREATE PROCEDURE [dbo].[sp_Send_CDOSysMail]
>
> @From varchar(4000),
>
> @To varchar(4000),
>
> @Subject varchar(4000)=" ",
>
> @Body varchar(4000) =" ",
>
> @Attachment varchar(4000) = " "
>
> /*********************************************************************
>
> This stored procedure takes the parameters and sends an e-mail.
>
> All the mail configurations are hard-coded in the stored procedure.
>
> Comments are added to the stored procedure where necessary.
>
> References to the CDOSYS objects are at the following MSDN Web site:
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosy...tml/_cd
>
> ***********************************************************************/
>
> 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.
>
> --
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosy...tml/_cd
>
> 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',
> 'smtp.va.gov'
>
> -- 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
>
> -- Check for Attachment specified and attach if necessary.
>
> IF @Attachment IS NOT NULL
>
> BEGIN
>
> EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', null, @Attachment
>
> -- EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @ATTACHMENT --@rv
> captures
> the method return and recieved emails with attachments without a problem.
>
> END
>
> -- 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 have to.
>
> -- Clean up the objects created.
>
> EXEC @hr = sp_OADestroy @iMsg
>
>
>
> --
> KPoku