dbTalk Databases Forums  

Using a trigger on sysjobhistory to send mail via cdosysmail

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.tools Using a trigger on sysjobhistory to send mail via cdosysmail

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools


Discuss Using a trigger on sysjobhistory to send mail via cdosysmail in the microsoft.public.sqlserver.tools forum.



Reply
Page 3 of 3 < 1 2 3
 
Thread Tools Display Modes
  #21  
Old   
opokad06
 
Posts: n/a

Default Re: Using a trigger on sysjobhistory to send mail via cdosysmail - 12-08-2008 , 10:32 AM





problem resolved,

Thank you all
--
KPoku


"opokad06" wrote:

Quote:
revised
--
KPoku


"Ekrem Önsoy" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:F7736B39-F590-4BF1-9C10-5638B0B0A91F (AT) microsoft (DOT) com...
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 = 'name (AT) gmail (DOT) com'

SELECT @EmailTo = 'name (AT) gmail (DOT) com'

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/de..._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/de..._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.com'

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

Reply With Quote
  #22  
Old   
opokad06
 
Posts: n/a

Default Re: Using a trigger on sysjobhistory to send mail via cdosysmail - 12-08-2008 , 10:32 AM





problem resolved,

Thank you all
--
KPoku


"opokad06" wrote:

Quote:
revised
--
KPoku


"Ekrem Önsoy" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:F7736B39-F590-4BF1-9C10-5638B0B0A91F (AT) microsoft (DOT) com...
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 = 'name (AT) gmail (DOT) com'

SELECT @EmailTo = 'name (AT) gmail (DOT) com'

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/de..._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/de..._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.com'

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

Reply With Quote
  #23  
Old   
opokad06
 
Posts: n/a

Default Re: Using a trigger on sysjobhistory to send mail via cdosysmail - 12-08-2008 , 10:32 AM


problem resolved,

Thank you all
--
KPoku


"opokad06" wrote:

Quote:
revised
--
KPoku


"Ekrem Önsoy" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:F7736B39-F590-4BF1-9C10-5638B0B0A91F (AT) microsoft (DOT) com...
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 = 'name (AT) gmail (DOT) com'

SELECT @EmailTo = 'name (AT) gmail (DOT) com'

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/de..._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/de..._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.com'

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

Reply With Quote
  #24  
Old   
opokad06
 
Posts: n/a

Default Re: Using a trigger on sysjobhistory to send mail via cdosysmail - 12-08-2008 , 10:32 AM


problem resolved,

Thank you all
--
KPoku


"opokad06" wrote:

Quote:
revised
--
KPoku


"Ekrem Önsoy" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:F7736B39-F590-4BF1-9C10-5638B0B0A91F (AT) microsoft (DOT) com...
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 = 'name (AT) gmail (DOT) com'

SELECT @EmailTo = 'name (AT) gmail (DOT) com'

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/de..._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/de..._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.com'

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

Reply With Quote
  #25  
Old   
opokad06
 
Posts: n/a

Default Re: Using a trigger on sysjobhistory to send mail via cdosysmail - 12-08-2008 , 10:32 AM


problem resolved,

Thank you all
--
KPoku


"opokad06" wrote:

Quote:
revised
--
KPoku


"Ekrem Önsoy" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:F7736B39-F590-4BF1-9C10-5638B0B0A91F (AT) microsoft (DOT) com...
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 = 'name (AT) gmail (DOT) com'

SELECT @EmailTo = 'name (AT) gmail (DOT) com'

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/de..._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/de..._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.com'

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

Reply With Quote
  #26  
Old   
opokad06
 
Posts: n/a

Default Re: Using a trigger on sysjobhistory to send mail via cdosysmail - 12-08-2008 , 10:32 AM


problem resolved,

Thank you all
--
KPoku


"opokad06" wrote:

Quote:
revised
--
KPoku


"Ekrem Önsoy" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:F7736B39-F590-4BF1-9C10-5638B0B0A91F (AT) microsoft (DOT) com...
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 = 'name (AT) gmail (DOT) com'

SELECT @EmailTo = 'name (AT) gmail (DOT) com'

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/de..._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/de..._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.com'

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

Reply With Quote
  #27  
Old   
opokad06
 
Posts: n/a

Default Re: Using a trigger on sysjobhistory to send mail via cdosysmail - 12-08-2008 , 10:32 AM


problem resolved,

Thank you all
--
KPoku


"opokad06" wrote:

Quote:
revised
--
KPoku


"Ekrem Önsoy" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:F7736B39-F590-4BF1-9C10-5638B0B0A91F (AT) microsoft (DOT) com...
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 = 'name (AT) gmail (DOT) com'

SELECT @EmailTo = 'name (AT) gmail (DOT) com'

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/de..._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/de..._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.com'

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

Reply With Quote
  #28  
Old   
opokad06
 
Posts: n/a

Default Re: Using a trigger on sysjobhistory to send mail via cdosysmail - 12-08-2008 , 10:32 AM


problem resolved,

Thank you all
--
KPoku


"opokad06" wrote:

Quote:
revised
--
KPoku


"Ekrem Önsoy" wrote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:F7736B39-F590-4BF1-9C10-5638B0B0A91F (AT) microsoft (DOT) com...
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 = 'name (AT) gmail (DOT) com'

SELECT @EmailTo = 'name (AT) gmail (DOT) com'

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/de..._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/de..._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.com'

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

Reply With Quote
Reply
Page 3 of 3 < 1 2 3

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top