WhereScape RED provides the ability to call a commandline program on successful completion or failure of a job. The most common use of this command is to send email to system administrators and operations staff (who care about it) when a job fails.
SQL Server 2005 and SQL 2008 have the ability to send emails via an SMTP server (using the system stored procedure SP_SEND_DBMAIL) which is quick and easy to set up.
Once a database administrator has set up a mail profile on the server then you can send either fancy emails or quick and dirty emails from the schduler. To send quick and dirty emails use a failure (or success) command in the job like this:
osql -S"(local)" -E -Q"EXEC msdb.dbo.sp_send_dbmail @profile_name='WS', @firstname.lastname@example.org', @subject = 'DEV WS Job: $JOB_NAME$ - FAILED'"
This will send an empty email with a subject that contains the job that failed. A fancy email can include job error information in the body of the email - this requires a stored procedure to generate HTML from the error logs in the WhereScape scheduler. In more detail:
1. Create the stored procedure ws_job_email from attached file in RED.
2. Edit your WhereScape RED job and enter the following into the Fail Command:
osql -S"(local)" -E -Q"EXEC dbo.admin_workflow_email @email@example.com', @subject='DEV WhereScape job: $JOB_NAME$', @RunStatus=0, @JobSequence=$JOB_SEQ$"
To use osql like this, its location has to exist in the scheduler user's path variable.