SMS Notifications | Boomerang Notification Services for SQL Services

Sending and Receiving SMS Messages

Introduction

SMS messaging is a great way to notify, alert and remind when ease of use and mobility is important and the amount of information needed to be transmitted is small. The Boomerang Notification Framework supports both outbound and inbound SMS messaging that will help IT professionals build solutions to distribute information and automate business processes.

Currently Boomerang support SMS messaging through Twilio’s cloud based SMS infrastructure. Twilio is a cloud communications company that provides a telephony infrastructure for Voice and SMS in most countries.

Sending SMS Messages

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 like SMS In and SMS Out has a corresponding table object that represents the service in question. So to send an basic SMS message through using Boomerang Notification Framework is an easy task and can be accomplished with three simple SQL statements.

  1. Create Event (EVENT_MASTER)
  2. Create Job (OUT_SMS)
  3. Release Event (EVENT_MASTER)

The below SQL statement will send a SMS message to phone number 555-555 5555.

---- Send SMS Message | SMS Notifications -----------------------
declare @gKey uniqueidentifier, @jKey uniqueidentifier
 
set @gKey = NEWID()
set @jKey = NEWID()
 
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Send SMS Demo')
insert OUT_SMS (gKey, jKey, [To], [Text]) 
values (@gKey, @jKey, '555-555 5555', 'Boomerang - Notification Awesomeness for IT pros!') 
update EVENT_MASTER set Status = 0 where gKey = @gKey
---- Send SMS Message | SMS Notifications -----------------------

Result (Apple iPhone):
SMS Out | SMS Notifications | Boomerang Notification Services for SQL Server

Sending SMS Messages from Multiple Numbers

If you have multiple phone numbers associated with your Twilio account you may dynamically select which phone number you will send the message from. If you omit OUT_SMS.From from your syntax the default number specified in the Boomerang Admin Console will be used.

The following statement will send two SMS message from two different phone numbers to the same recipient.

---- Send SMS Message | SMS Notifications -----------------------
declare @gKey uniqueidentifier, @jKey1 uniqueidentifier, @jKey2 uniqueidentifier
 
set @gKey = NEWID()
set @jKey1 = NEWID()
set @jKey2 = NEWID()
 
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Send SMS Demo')
 
insert OUT_SMS (gKey, jKey, [From], [To], [Text]) 
values (@gKey, @jKey1, '+16156516963', '555-555 555', 'Message from first phone number')
 
insert OUT_SMS (gKey, jKey, [From], [To], [Text]) 
values (@gKey, @jKey2, '+16156516964', '555-555 555', 'Message from second phone number')
 
update EVENT_MASTER set Status = 0 where gKey = @gKey
---- Send SMS Message | SMS Notifications -----------------------

Dynamically Assemble a SMS Message

There are many business situations that would benefit from a timely alert or notification. For example you may want alert your customers when a price is lowered or notify when a delivery has been made.

In this particular example we are going to alert a database administrator that there are sql-queries that have not yet been able to run due to a database lock.

We start by declaring our variables and querying for any long running transactions (more than 1 minute) by using sys.dm_exec_requests. The jKey and gKey will be needed in the next step when we are sending the actual SMS.

---- Send SMS Message | SMS Notifications -----------------------
declare @gKey uniqueidentifier; set @gKey = newid()
 
Select 
 session_id, blocking_session_id, newid() as jKey, @gKey as gKey
,'SQL Locking Alert' + char(10) + char(13) +
 'Session Id: ' +  cast(session_id as varchar(4)) + char(10) + char(13) +
 'Blocking Id: ' + cast(blocking_session_id as varchar(10)) + char(10) + char(13) +
 'Blocked for: ' + cast(convert(decimal(3,2), wait_time/60000.00) as varchar (10)) + ' Minutes' as Msg
into #tmp_sql_locks
from sys.dm_exec_requests 
where blocking_session_id != 0  -- only locked transactions
and wait_time > 60000 -- 1 minute

Result:

session_id blocking_session_id jKey                                 gKey                                 Msg
---------- ------------------- ------------------------------------ ------------------------------------ --------------------------------
67         68                  8C6AA86A-474A-401E-BC46-D9ABE938B7A1 32BBC292-0BE7-4CAA-ABCF-6CCAF4A278C0 SQL Locking Alert
                                                                                                         Session Id: 67
                                                                                                         Blocking Id: 68
                                                                                                         Blocked for: 4.65 Minutes
68         70                  54199FB1-0CA6-4F30-A81B-52747E87F117 32BBC292-0BE7-4CAA-ABCF-6CCAF4A278C0 SQL Locking Alert
                                                                                                         Session Id: 68
                                                                                                         Blocking Id: 70
                                                                                                         Blocked for: 4.70 Minutes
 
(2 row(s) affected)

Next we create a new event and insert all records into OUT_SMS. Finally we update the event as “ready to be sent” by updating the EVENT_MASTER.Status column. This will release the SMS job and Boomerang Notification Services will process it.

if @@ROWCOUNT = 0 begin return end
 
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'SQL Locking Alert')
 
insert OUT_SMS (gKey, jKey, [To], [Text]) 
select gKey, jKey, '555-555 5555', left(Msg, 160) from #tmp_sql_locks
 
update EVENT_MASTER set Status = 0 where gKey = @gKey
---- Send SMS Message | SMS Notifications -----------------------

Result (Android Phone):
SMS Out | SMS Notifications | Boomerang Notification Services for SQL Server

Receiving SMS Messages

All SMS messages sent to any of the phone numbers associated with your Twilio account will be stored in the IN_SMS table. This will happen automatically and does not require any actions other than configuring SMS services.

The below SMS message sent to +1 615 651 6963 will be captured and stored in the Boomerang SQL database
SMS In | SMS Notifications | Boomerang Notification Services for SQL Server
Query:

--- Query Inbound SMS Messages ---------------------------
select * from IN_SMS
--- Query Inbound SMS Messages ---------------------------

Result:

rKey                                 Created_On              ID                                                 From                                               To                                                 Text                                                                                                                                                             Processed_On            Price
------------------------------------ ----------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------
854025FD-1EF3-464F-A2E4-5D31FC0FE446 2013-08-13 17:58:39.953 SMca10dab223d46ecb76c13872e88e35c1                 +15555555555                                       +16156516963                                       Hello world!                                                                                                                                                     2013-08-13 17:58:28.000 -0.01
 
(1 row(s) affected)

Processing Inbound SMS Messages

Like all other services in Boomerang the SMS services comes with triggers to simplify and speed up the development process. For example when a SMS message has been successfully received by Boomerang the trigger sp_On_SMS_In is fired.

To further process or automate a response can in other words be done with just a few sql statements. So for example responding to a tech service request could look something like this:

ALTER procedure [dbo].[sp_On_Sms_In]
(
  @rKey uniqueidentifier
)
as begin
 
-- Demo Code Automatic Reply to Inbound SMS Messages --------------------------------------
declare @ReplyToNumber varchar(50), @InboundMsg nvarchar(160), 
@gKey uniqueidentifier, @jKey uniqueidentifier, @Msg nvarchar(160)
 
--- Get senders phone number and message content --------
select @ReplyToNumber = [From], @InboundMsg = [Text] from [dbo].[IN_SMS] where [rKey] = @rKey
 
if (@InboundMsg like '%What is the status of my tech issue?%') 
	begin  
	set @Msg = 'Hello there! A team of highly trained monkeys has been dispatched to deal with the situation'
	end
 
if (@InboundMsg like '%Are you sure?%') 
	begin  
	set @Msg = 'No doubt'
	end
 
if (@Msg is not null)
	begin
	----- Send a reply SMS message -------
 
	set @gKey = NEWID()
	set @jKey = NEWID()
 
	insert EVENT_MASTER(gKey, Str1) values (@gKey, 'Send SMS reply')
	insert OUT_SMS (gKey, jKey, [To], [Text]) values (@gKey, @jKey, @ReplyToNumber, @Msg) 
	update EVENT_MASTER set Status = 0 where gKey = @gKey
	end
 
end

Result:
SMS In/Out | SMS Notifications | Boomerang Notification Services for SQL Server

Threaded Conversations

To query a particular SMS conversation you can do a union between the IN_SMS and the OUT_SMS tables. The following code snippet tracks a conversation between phone number 555-555 5555 and Boomerang’s SMS services (615-651 6963) from a specific date (July 2nd 2013).

--- Threaded Conversation SMS In and Out Demo Code ------------------
declare @phone1 varchar(50), @phone2 varchar(50), @beg datetime
 
select @phone1='+15555555555', @phone2='+16156516963', @beg='20130702'
 
select Processed_On, [From], 'in ' Direction, [Text] from IN_SMS  where [From]=@phone1 and [To]=@phone2 and Processed_On>=@beg
union all
select Processed_On, [From], 'out' Direction, [Text] from OUT_SMS where [To]=@phone1 and [From]=@phone2  and Processed_On>=@beg
order by 1
--- Threaded Conversation SMS In and Out Demo Code ------------------

Result:

Processed_On            From                                               Direction Text
----------------------- -------------------------------------------------- --------- --------------------
2013-08-14 11:22:59.000 +15555555555                                       in        Hello
2013-08-14 11:24:06.000 +16156516963                                       out       Hello
                                                                                     Msg Count: 1
2013-08-14 11:25:24.000 +15555555555                                       in        Hello again
2013-08-14 11:25:51.000 +16156516963                                       out       Hello again
                                                                                     Msg Count: 2
2013-08-14 11:26:18.000 +15555555555                                       in        Hello a third time
2013-08-14 11:27:09.000 +16156516963                                       out       Hello a third time
                                                                                     Msg Count: 3
 
(6 row(s) affected)

Screen Capture
SMS In/Out | SMS Notifications | Boomerang Notification Services for SQL Server

Combining Multiple Notification Services

You may combine the SMS In and SMS Out services with other Boomerang Services like Email Out, Twitter In, File Out or Fax Out. You may use multiple services in parallel, series or as a combination of these two methods.

Parallel Sequencing

To use several services in parallel simply create a new Event, specify which services to use and finalize the event.
Example:

---- Send SMS Message and Post Twitter Status Update --------------------
declare @gKey uniqueidentifier, @jKey1 uniqueidentifier, @jKey2 uniqueidentifier
 
set @gKey = NEWID()
set @jKey1 = NEWID()
set @jKey2 = NEWID()
 
insert EVENT_MASTER(gKey, Str1) values (@gKey, 'SMS/Twitter Demo')
 
insert OUT_SMS (gKey, jKey, [From], [To], [Text]) 
values (@gKey, @jKey1, '+16156516963', '+31646394737', 'SMS Message')
 
insert OUT_TWIT(gKey, jKey, Status) 
values (@gKey, @jKey2, 'Twitter Status Update')
 
update EVENT_MASTER set Status = 0 where gKey = @gKey

Result:
SMS Out | SMS Notifications | Boomerang Notification Services for SQL Server

Twitter Out | SMS Notifications | Boomerang Notification Services for SQL Server6

Serial Sequencing

The easiest way to trigger multiple services in a series would be to use the built in Event Handler. In the following example a chain of notifications is triggered by an inbound SMS message.

Event Event Handler Example Service
1. On Inbound SMS Message dbo.sp_On_Sms_In When SMS message is received send Email Out OUT_EMAIL
2. After Email Sent dbo.sp_After_Email_Job Print Email content OUT_PRINT
3. After Printing dbo.sp_After_Print_Job Fax Email content OUT_FAX
4. On Fax Status Change dbo.sp_On_Fax_Status When Fax massage transmitted successfully send SMS message OUT_SMS

About Boomerang Notification Framework

Boomerang is a collection of notification services for creating applications that generate and send, as well as receiving notifications. Using the Boomerang notification framework, you can quickly create applications to generate and send notifications to customers, suppliers, partners and employees.

The application interface of Boomerang is entirely based on SQL Server (MS) meaning that each notification service has a corresponding table object that represents the service in question. For example to send and email out you would insert a record into the OUT_EMAIL table. To send a fax you would insert a record into the OUT_FAX table.

For More Informantion

For detailed reference documentation and other support resources visit www.fuel9.com/support

Download Sample

Download the above examples.