Blueprint: Creating email alerts for non-System Health events.

Goal:

Create an email alert for a non-System Health event (failed publication, message alert, and so on).

Environment:

Pharos Blueprint v4.1

Pharos Blueprint v4.2

Pharos Blueprint v5.0

Symptoms:

No email alert sent for events that do not populate the "Overview" context.

Resolution:

Most any event that results in a record in a database table can create a notification using SQL Server's Database Mail function combined with a SQL trigger.

NOTE: SQL triggers may not be supported when performing database updates/upgrades due to a new version release of Pharos Blueprint software. It is also important that a full backup of the affected database be performed prior to running any scripts. It is highly advised to test any trigger in a non-production system prior to including it in production. A trigger can, based on its criteria, create an undue strain on server performance.

Here is a sample trigger that emails a user if a Publication fails:

USE psbprint
GO
CREATE TRIGGER TP_PublicationFailed
ON DataTransformHistory
FOR INSERT
AS
  declare @When datetime
  declare @Body varchar(2000)
  declare @ErrorText nvarchar(1024)
SELECT @When=LastRunDate
FROM inserted
WHERE Outcome=0
  set @Body='The publication initated on ' @When ' has failed with error message ' @ErrorText '.'
EXEC master..xp_send_dbmail
  @profilename='ProfileCreatedInWizard',
  @recipients='blueprintadmin@company.com',
  @subject='Blueprint Publication Fail',
  @message=@Body
GO

In order for this Trigger to work, xp_send_dbmail must be enabled and the server must be set up for an SMTP server. The Database Mail wizard (found in SQL Management Studio > SQL Server name > Management > Database Mail) will enable the core function and provide the Profile Name for the sample script (@profilename value). Once the SQL Server has been configured, run the following:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO