Re: Using a trigger on sysjobhistory to send mail via cdosysmail

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" <opokad06@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:F7736B39-F590-4BF1-9C10-5638B0B0A91F@xxxxxxxxxxxxxxxx
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@xxxxxx'

SELECT @EmailTo = 'Kwame.Adu-Poku@xxxxxx'

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/cdosys/html/_cdosys_messaging.asp

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

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/cdosys/html/_cdosys_schema_configuration_sendusing.asp

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

.

Relevant Pages