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