sql server notification services: 'Pushing' messages to multiple recipients -- sql server notification services

'Pushing' messages to multiple recipients

Royd
9/9/2004 4:35:13 AM

sql server notification services: I'm currently engaged in using Notification Services to send alerts from an
application. The straightforward model of users 'subscribing' to what their
interested in and matching events with a rule is fine in some situations
however in others we wish to be able to push messages to users.
As an example our app. manages traning course bookings, we wish to
automatically notify the people booked if a corse is canceled. As I see it we
could do this by automatically subscribing users to 'CourseCanceled' event
when they book, however this means have to cleanup these subscriptions later
after a course has taken place (functinality not included in NS as far as I
can see).
The other option (similar to that used in the MS Enterprise Notification
Refererence Architecture) means creating both a 'CourseCancel' event and a
'RecipientCourseCancel' event. In this senario the first CourseCancel event
inserts into a chronical table the course details together with an EventID (a
GUID generated within a custom event provider) following this we add
RecipientCourseCancel events for each recipient with each of these including
the EventID to match against. This still means subscribing users to a (let
say) CancelCoursesImBookedOnSubscription (but at least this is more generic
and does not need to be cleaned up) however it has the down side of having to
create two events (the event its self and also the 'recipient' event) for
each and every senario like this. The reference architecture mentined above
simlifies this by have one 'message event' that includes a 'message body'
field, however this means you loose the ability to provide localisation with
different .xslt formaters as the message has already been formatted at event
time.
Can anyone confirm my investigations as correct? and does anyone have any
other suggestions for other solutions to senarios like this, I would have
thought this is quite common as users may not subscribe to something they
dont think they will need to know.

Hope all this makes sence,

Re: 'Pushing' messages to multiple recipients

Colin Meek [MSFT]
9/9/2004 9:13:30 AM

To paraphrase: when a "course cancelled" event arrives, you wish to notify
all people enrolled in the class. There are many ways of tackling this
problem, and the strategy you suggest should work fine.

It might be simpler however to produce notifications directly from the
original "course cancelled" event. You do not need a subscription to produce
a notification. Here is a simple example, which glosses over
implementation-specific details:

A table contains enrollment information:

Enrollments( SubscriberId nvarchar(255), CourseId bigint, ... )

The "course cancelled" event:

CancelledCourse( CourseId bigint, ... )

The "CancelledCourse" <EventRule><Action>:

SELECT dbo.CancelledCourseNotificationNotify(e.SubscriberId, ...)
FROM CancelledCourse cc, Enrollments e
WHERE cc.CourseId = e.CourseId

- Colin Meek [MSFT]

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only.
--

[quoted text, click to view]