CSI: Email Notification when Job is Completed at No Cost

unknown

Trying to create an Event Handler that will send a notification when a job is posted with no cost.  Has anyone done this and have a working example?

dean-bradko

Are you using cloud or on-prem?  I'm on-prem and have done similar things with different IDO's.  For example, our AR department will get an e-mail when a cutomer order goes on hold.  I accomplish this by calling [msdb].[dbo].[sp_send_dbmail] from an AfterUpdate trigger on the co_mst table.  More accurately, I call a custom stored proc that defaults much of the values for the sp_send_dbmail proc.  I find this easier than calling Infor's SendMailSp.  This will require you to setup

Doing this in a trigger is probably considered bad practice but it works well for us.

You could also easily create a wrapper for yourself and call it directly from an event handler which we've also done..  My wrapper looks similar to the following:

ALTER PROCEDURE [dbo].[PWP_SendDbMailsp]
   @profile_name              sysname    = NULL,        
   @recipients                VARCHAR(MAX)  = NULL,
   @subject                   NVARCHAR(255) = NULL,
   @body                      NVARCHAR(MAX) = NULL
  WITH EXECUTE AS 'dbo'
AS
BEGIN
DECLARE @RC INT = 0,
   @copy_recipients           VARCHAR(MAX)  = NULL,
   @blind_copy_recipients     VARCHAR(MAX)  = NULL,
   @body_format               VARCHAR(20)   = NULL,
   @importance                VARCHAR(6)    = 'NORMAL',
   @sensitivity               VARCHAR(12)   = 'NORMAL',
   @file_attachments          NVARCHAR(MAX) = NULL,  
   @query                     NVARCHAR(MAX) = NULL,
   @execute_query_database    sysname       = NULL,  
   @attach_query_result_as_file BIT          = 0,
   @query_attachment_filename NVARCHAR(260) = NULL,  
   @query_result_header       BIT           = 1,
   @query_result_width        INT           = 256,            
   @query_result_separator    CHAR(1)       = ' ',
   @exclude_query_output      BIT           = 0,
   @append_query_error        BIT           = 0,
   @query_no_truncate         BIT           = 0,
   @query_result_no_padding   BIT           = 0,
   @mailitem_id              INT            = NULL,
   @from_address              VARCHAR(max)  = NULL,
   @reply_to                  VARCHAR(max)  = NULL


EXEC @RC = [msdb].[dbo].[sp_send_dbmail]
   @profile_name,        
   @recipients,
   @copy_recipients,
   @blind_copy_recipients,
   @subject,
   @body,
   @body_format,
   @importance,
   @sensitivity,
   @file_attachments,  
   @query,
   @execute_query_database,  
   @attach_query_result_as_file,
   @query_attachment_filename,  
   @query_result_header,
   @query_result_width,            
   @query_result_separator,
   @exclude_query_output,
   @append_query_error,
   @query_no_truncate,
   @query_result_no_padding,
   @mailitem_id,
   @from_address,
   @reply_to

   RETURN @RC
END

This will require you to setup Database Mail in SQL Server as shown below: