Notification Awesomeness in 3 Easy Steps | Boomerang Notification Services for SQL Server

Notification Awesomeness in 3 Easy Steps

Boomerang is a bolt-on solution for MS-SQL Server. The application interface of Boomerang is entirely based on SQL Server meaning that each notification service has a corresponding table object that represents the service in question.

Since Boomerang Notification Services is based on SQL all features of SQL and all business data you store in SQL are at your fingertips to create powerful and dynamic notifications in minutes.

The following example illustrates how you can send an email using a handful lines of simple T-SQL statements. The steps involved are the same regardless if you send an email, print a document, fax an SSRS report or any of the other outbound notification services.

Boomerang Notification Services | In 3 Easy Steps

Step 1

Declare keys and create a new Event by insert a record into table EVENT_MASTER. The keys (unique identifier) are used to link Event, Jobs and Event Content together.

---- Declare keys -----------------------------------------------------
declare @gKey uniqueidentifier; set @gKey = newid();
declare @jKey uniqueidentifier; set @jKey = newid();
declare @aKey uniqueidentifier; set @aKey = newid();
 
---- New Event --------------------------------------------------------
Insert EVENT_MASTER (gKey, Created_By, Str1)
values (@gKey, 'domain\username', 'Weekly Sales Report');

Step 2

Create a Job by inserting a record into one of the tables for outbound notifications; OUT_EMAIL, OUT_FAX, OUT_FILE, OUT_TWIT, OUT_PRINT or OUT_TXT. You may also add additional Jobs in the same Event. You will do this by adding another insert (new jKey) that reference the same gKey.

---- Create e-mail job ------------------------------------------------
insert OUT_EMAIL (gKey, jKey, Subject, Body)
values(@gKey, @jKey, 'Weekly Sales Report', 'Message body test test test');

Depending on the type of Job additional information might be needed. For example in the case of sending emails one or more recipients should be added to OUT_EMAIL_RECIPIENT.

---- Add recipients to e-mail -----------------------------------------
insert OUT_EMAIL_RECIPIENT (jKey, Email)
values (@jKey, 'e-mail_address@domain.com');

Depending on your requirements you may want to add additional content like a file or a SSRS report to the Job. You do this by adding a row into EVENT_CONTENT.

---- Add report attachment as a PDF to e-mail -------------------------
insert EVENT_CONTENT (gKey, jKey, aKey, Src_Type, Path, [Format]) 
values (@gKey, @jKey, @aKey, 2, 'SALES/Weekly Sales Report', 'PDF');

Since this particular SSRS report requires a parameter you will need to add a row into CONTENT_PARAMETER.

---- Add parameters to report -----------------------------------------
insert CONTENT_PARAMETER (aKey, [Name], [Value])
values (@aKey, 'Week', '12');

Step 3

Unless you want to add more Jobs to this Event you flag now flag this Event ready to be processed. You do this by changing the Status (from -1) to 0

---- Release task to be processed -------------------------------------
update EVENT_MASTER set Status=0 where gKey=@gKey;

Result

Weekly Sales Example | Boomerang Notification Services for SQL Server

More Code Samples

For more in depth and detailed examples how to work with Boomerang visit the Feature Sample page.