Success/Failure Email from Scheduled Job (SQL Server)

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