December 15, 2011 at 4:07 am
I have a Bookings table as such:-
CREATE TABLE [dbo].[Bookings](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [smalldatetime] NOT NULL,
[Start] [smalldatetime] NOT NULL,
[Duration] [smallint] NOT NULL,
[Status] [varchar](50) NOT NULL,
CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Bookings] ADD CONSTRAINT [DF_Bookings_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[Bookings] ADD CONSTRAINT [DF_Bookings_Status] DEFAULT ('TBA') FOR [Status]
GO
I have the following data:-
insert into bookings (Start, Duration) values ('2011/12/15 10:00', 90)
insert into bookings (Start, Duration, Status) values ('2011/12/15 11:00', 120, 'Covered')
insert into bookings (Start, Duration, Status) values ('2011/12/16 09:00', 240, 'Cancelled')
insert into bookings (Start, Duration) values ('2011/12/17 12:00', 120)
insert into bookings (Start, Duration) values ('2011/12/18 15:00', 120)
I'm trying to find a way to send alert notifications (either email, sms, push notifications, etc) via my application, if one of my bookings is still TBA and has been on the system for longer than 90 minutes.
I would rather stay away from polling the database every minute or so as it degrades performance of my application as i have over a million bookings, so i'm looking for a more elegant solution.
I have been trying to make use of Query Notifications to monitor for changes in my Bookings table, which works to a certain extent, but i'm having issues working out how to notify when a TBA booking has been on the system for longer than 90 minutes.
So far my query is as below. The 2011/12/15 11:00 is the current time that my app dynamically passes to my query.
select Id
from dbo.Bookings
where Status = 'TBA'
and Start between convert(datetime, '2011/12/15 11:00', 20) and dateadd(d, 7, convert(datetime, '2011/12/15 11:00', 20))
and DateCreated < convert(datetime, '2011/12/15 11:00', 20)
order by Start
This does initially return TBA bookings for the next 7 days that were created over an hour and a half ago.
As bookings get created and amended via my app (bookings can be returned back to TBA), then as the date they get created is never going to be prior to the current date time that i pass, then i'll never receive notifications.
Can anyone think of a way to do this?
September 19, 2012 at 6:13 am
You've probably over simplified your example table, as I don't see an account column in it. I would expect a bookings table to have an account column in it, to identify onto which account the booking was placed.
You can -using service broker- write a notification service that is sent notification messages with each insert/update/delete on the Bookings table. The notification messages can then be used to keep track of the accounts that need to be alerted. If you use another service broker service to keep track of the individual accounts that are 'active' (i.e. have a status that may need alerting at some time) you can set a timer on each account's conversation. The service updates the status of each account when an notification comes in. Depending on the status received either a new conversation is started plus a timer is started on the new conversation (= account enters TBA status), the conversation may be ended (= account leaves 'active' states), or the timer may be reset (= account receives a repeated TBA status while still in an 'active' state). Now when the timer expires, a DialogTimer message is sent out by service broker on this conversation and your service can perform the required sms/email/ or whatever actions. This way the system will always only execute any queries when an actual change is made or an action is required, so the additional load is as low as possible, plus using service broker you can even off-load some of the tasks to another physical machine.
Here's a lengthy script I wrote to test the DIY notifications. I am not going to put any more explanations with this script. I intend to write an article describing this technique, but I put this scrit online already for anyone interested to have a look at it.
CREATE TABLE [dbo].[Bookings](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [smalldatetime] NOT NULL,
[Start] [smalldatetime] NOT NULL,
[Duration] [smallint] NOT NULL,
[Status] [varchar](50) NOT NULL,
CONSTRAINT [PK_Bookings] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING On
GO
ALTER TABLE [dbo].[Bookings] ADD CONSTRAINT [DF_Bookings_DateCreated] DEFAULT (getutcdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[Bookings] ADD CONSTRAINT [DF_Bookings_Status] DEFAULT ('TBA') FOR [Status]
GO
alter table dbo.Bookings add
AccountNumber char(11) not null;
go
create table dbo.BookingNotificationSubscriptions (
conversation_handle uniqueidentifier not null,
constraint pkBookingNotificationSubscriptions primary key clustered (conversation_handle)
)
go
create message type [http://demo.company.com/messages/BookingNotification]
authorization dbo
validation = well_formed_xml;
go
create contract BookingNotificationService
authorization dbo
(
[http://demo.company.com/messages/BookingNotification] sent by initiator
)
go
create queue dbo.[BookingNotificationsQueue]
go
create service [BookingNotifications]
authorization dbo
on queue dbo.[BookingNotificationsQueue]
(
BookingNotificationService
)
go
create trigger taiBookings_notification
on dbo.Bookings
for insert
as
begin
if @@rowcount = 0
return;
set nocount on;
if not exists(
select top 1 *
from dbo.BookingNotificationSubscriptions
)
return;
declare @xml xml;
with xmlnamespaces( 'http://demo.company.com/messages/BookingNotification/1/0' as bn)
select @xml = (
select i.AccountNumber as [bn:Account],
i.Start as [bn:Start],
i.Duration as [bn:Duration],
i.Status as [bn:Status]
from inserted i
for xml path('bn:BookingNotification'), root('bn:Notifications'), type
);
declare cur cursor local fast_forward
for
select conversation_handle
from dbo.BookingNotificationSubscriptions;
open cur;
while 1 = 1
begin
declare @conversation_handle uniqueidentifier;
fetch next from cur into @conversation_handle;
if @@fetch_status = -1
break;
if @@fetch_status = 0
begin
send on conversation @conversation_handle message type [http://demo.company.com/messages/BookingNotification] (@xml);
end
end
close cur;
deallocate cur;
end
go
-- insert a bookings record.
insert into bookings (AccountNumber, Start, Duration) values ('11111111111', '2011/12/15 10:00', 90);
go
-- Demonstrate that no notification message(s) were created yet (since no subscription exists).
select *, convert(xml, message_body)
from dbo.BookingNotificationsQueue with (nolock)
go
-- Now start a subscription (should be done in an sp or such).
begin tran trnNewSubscription;
begin try
declare @conversation_handle uniqueidentifier;
begin dialog conversation @conversation_handle
from service [BookingNotifications]
to service N'BookingNotifications'
on contract [BookingNotificationService]
with encryption = off;
insert dbo.BookingNotificationSubscriptions(conversation_handle)
values( @conversation_handle);
commit tran trnNewSubscription;
end try
begin catch
if xact_state() > 0
rollback tran trnNewSubscription;
else if xact_state() < 0
rollback tran;
select error_number(), error_message();
end catch
go
-- Insert another booking row
insert into bookings (AccountNumber, Start, Duration, Status)
values ('11111111111', '2011/12/15 11:00', 120, 'Covered')
go
-- now demo that a notification is created when a record is inserted.
select *, convert(xml, message_body)
from dbo.BookingNotificationsQueue with (nolock)
go
-- Demo that it works for single line inserts and multiple as well.
insert into bookings (AccountNumber, Start, Duration, Status) values ('11111111111', '2011/12/16 09:00', 240, 'Cancelled')
insert into bookings (AccountNumber, Start, Duration) values ('11111111111', '2011/12/17 12:00', 120)
insert into bookings (AccountNumber, Start, Duration) values ('11111111111', '2011/12/18 15:00', 120)
insert into bookings (AccountNumber, Start, Duration, Status) values ('11111111111', '2011/12/16 09:00', 240, 'Cancelled')
insert into bookings (AccountNumber, Start, Duration)
select '11111111111', '2011/12/17 12:00', 120
union all
select'11111111112', '2011/12/18 15:00', 120
go
set statistics io, time on;
insert into bookings (AccountNumber, Start, Duration, Status) values ('11111111111', '2011/12/16 09:00', 240, 'TBA');
set statistics io, time off;
go
-- Show the generated notifications in the queue.
select *, convert(xml, message_body)
from dbo.BookingNotificationsQueue with (nolock)
go
-- A table that will keep track of the active accounts only.
create table dbo.ActiveAccounts (
Account char(11) not null,
Status varchar(50) null
);
-- Now create a queue reader to handle the notifications.
alter procedure dbo.spBookingNotificationHandler
as
begin
set nocount on;
declare @h uniqueidentifier,
@messageTypeName sysname,
@messageBody varbinary(max),
@xmlBody xml,
@procedureName sysname,
@startTime datetime,
@finishTime datetime,
@token uniqueidentifier;
declare @bookings table (
[Account] [char](11) not null,
[Start] [smalldatetime] not null,
[Duration] [smallint] not null,
[Status] [varchar](50) not null
);
while 1 = 1
begin
begin tran;
waitfor(
receive top(1)
@h = conversation_handle,
@messageTypeName = message_type_name,
@messageBody = message_body
from dbo.BookingNotificationsQueue
), TIMEOUT 10000; -- Stop waiting if no new message arrives
-- within 10 seconds after the last.
if not @@rowcount > 0
begin
commit tran;
break; -- exit while 1 = 1
end
if (@messageTypeName = N'http://demo.company.com/messages/BookingNotification')
begin
select @xmlBody = CAST(@messageBody as xml);
-- Always make sure the table is empty before we start!
delete @bookings;
-- Read the contents from the xml into a memory table for easier processing.
with xmlnamespaces( 'http://demo.company.com/messages/BookingNotification/1/0' as bn)
insert @bookings( Account, Start, Duration, Status)
select b.n.value('bn:Account[1]','char(11)'),
b.n.value('bn:Start[1]','smalldatetime'),
b.n.value('bn:Duration[1]','smallint'),
b.n.value('bn:Status[1]','varchar(50)')
from @xmlBody.nodes('bn:Notifications/bn:BookingNotification') b(n);
-- TODO: put here whatever you need done on the bookings we just were notified of.
-- This demo updates an ActiveAccounts table.
-- In this case keep track of the current status for active accounts.
-- Add any accounts that have gone into an active status but are not in the
-- accounts table yet.
-- I do a group by on the account number to avoid the situation where in
-- one notification message multiple statusses are reported; this should
-- normally never happen. But if it does, it could create duplicates and
-- thereby give us errors.
insert dbo.ActiveAccounts( Account, Status)
select b.Account, min(b.Status)
from @bookings b
where not b.Status in ('Cancelled','Covered')
and not exists (
select *
from dbo.ActiveAccounts a
where a.Account = b.Account
)
group by b.Account;
-- Remove all account numbers from the ActiveAccounts table that are no
-- longer active.
delete a
from dbo.ActiveAccounts a
where a.Account in (
select b.Account
from @Bookings b
where b.Status in ('Cancelled','Covered')
);
-- Do NOT put an end conversation here: end conversation will end the subscription!
end
else if (@messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
begin
delete s
from dbo.BookingNotificationSubscriptions s
where s.conversation_handle = @h;
end conversation @h;
end
-- Don't forget to handle error messages too.
-- These also indicate the end of a conversation just like EndDialog.
else if (@messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
begin
delete s
from dbo.BookingNotificationSubscriptions s
where s.conversation_handle = @h;
-- Read the xml document that we were sent in the message body.
declare @nCode int;
declare @vchDescription nvarchar(2000);
declare @xmlError xml; -- (document
select @xmlError = convert(xml, @messageBody);
with xmlnamespaces( default 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
select
@nCode = Errors.error.value( 'Code[1]', 'int'),
@vchDescription = Errors.error.value( 'Description[1]', 'nvarchar(max)')
from @xmlError.nodes( '/Error[1]') as Errors(error);
-- Any text output by a SSSB queue reader will by default end up in the sql log,
-- so I just write out the output with nowait to get errors shown in the sql log.
-- Using "with log" would put the error in the sql log twice: once because of
-- the with log, plus once more because all output from an auto activated procedure
-- is written into the log. So don't specify with log here.
raiserror('Service broker error received. Error %d:%s. (%s)', 0, 0, @nCode, @vchDescription, '%PROC%') with nowait;
-- An Error message means the conversation has ended, just like an EndDialog message.
-- So we tell SSSB we got the message and have it clean up our end of the conversation too.
end conversation @h;
end
commit tran;
end
end
go
-- Show what it does by letting it process the current queue content.
exec dbo.spBookingNotificationHandler;
go
select * from dbo.Bookings
select * from sys.conversation_endpoints
select * from dbo.BookingNotificationSubscriptions
select * from dbo.BookingNotificationsQueue
select * from dbo.ActiveAccounts
go
-- Have the queue reader auto active upon receipt of a notification message.
alter queue dbo.BookingNotificationsQueue
with activation (
status = on,
procedure_name = dbo.spBookingNotificationHandler,
execute as owner,
max_queue_readers = 2
);
go
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply