WhereScape Builds data warehouses right, now

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’, @recipients=’dbarrett@wherescape.com’, @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 @recipients=’dbarrett@wherescape.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.