Success/Failure Email from Scheduled Job (SQL Server)

Modified on Fri, 24 Mar 2017 at 11:12 AM


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

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article