This provides an example of sending an email summary of a job once it completes.  This utilizes the Success & Failure Commands available on a job, and Microsoft SQL Server Database Mail.


Pre-Requisites

Microsoft SQL Server Database Mail has been configured and tested


Installation

  • Create a new top-level on the WhereScape RED Scheduler server (e.g. D:\WhereScape\)
  • Download the WSRED_Job_eMail.zip and save to the new folder
  • Extract the files from the zip file into the folder.
  • Create a new procedure in WhereScape RED named ws_job_email.
  • Paste the contents of ws_job_email.SQL into the the procedure
  • Save & Compile the procedure
  • Create a parameter MAIL_PROFILE_NAME, specify the name of the mail profile for SQL Server Database Mail.
  • Edit the file SendMail.BAT to ensure folder names are correct


Limitations

  • Job Names must have no spaces
  • Full length of the job Success Command/Failure Command cannot exceed 256 characters


Configuring Jobs for Email

In WhereScape RED, edit the Job and put the following in the Failure Command

 

D:\WhereScape\SendMail.BAT <DBInstance> <DBName> <eMailAddr> $JOB_NAME$ $JOB_SEQ$ 0


<DBInstance> - The Database Server & instance name (e.g. Localhost or MyServer\SQL2014)

<DBName> -  The name of the WhereScape RED Metadata Database (e.g. WslWarehouse)

<eMailAddr> - The destination email address for the failure notification (e.g. Support@MyCompany.com)

 


Sample eMail Output