|
|
You're in the
sql server notification services
group:digest notifications for different events
sql server notification services:
we're currently designing a notification system for our document management product, and we're thinking about using SQL NS instead of developing a home-brewn subscription engine. SQL NS looks very promising to us. However, one of the critical requirements for our subscription system is the ability to bundle multiple notifications into a single e-mail message. SQL NS does support digest delivery, which is able to bundle all notifications for a single event class into one message. That would be very nice, if I would have only one event class. However, I've got a whole bunch of different events happening in the document management system. For example: - new document is added - document is changed - document is due to expire and so on.. Our users can subscribe to any combination of these events, however, they don't want to receice separate e-mails for every event type. They don't want a mail which tells them which documents are added, and another mail telling them which documents are changed. Instead of separate mails, I need to be able to combine all notifications into one single message. As far as I can see, there is no way I can change the way SQL NS determines which notifications can be digested into one single notification. This leaves me with 2 alternatives: 1) not using SQL NS 2) use SQL NS, and build a custom delivery protocol. The custom delivery protocol would store all notifications into a database table. While the delivery protocol handles all subscriptions, notifications for all event types are collected together in this database. Following to that, I would run a "GROUP BY subscriber" query on this table to get the notifications for each user. Finally, I would create an e-mail message for each user, containing the grouped notifications, send this message via SMTP, and clean up the table. I don't like option 1), because it would cost us a lot to develop our own system, and especially make it perform as well as SQL NS. Option 2) isnt' that great either. So, are there any alternatives to solve this problem ? regards, Hilbert
Can you change your event class model so that it passes another
parameter indicating the type? For example, a ChangeType parameter where 1 = new doc added, 2 = doc is changed, or some such? -- Joe Webb SQL Server MVP http://www.sqlns.com ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 I support PASS, the Professional Association for SQL Server. (www.sqlpass.org) [quoted text, click to view] On 1 Mar 2006 12:56:35 -0800, "gotcha" <hleijen@gmail.com> wrote:
>Hi, > >we're currently designing a notification system for our document >management product, and we're thinking about using SQL NS instead of >developing a home-brewn subscription engine. > >SQL NS looks very promising to us. However, one of the critical >requirements for our subscription system is the ability to bundle >multiple notifications into a single e-mail message. >SQL NS does support digest delivery, which is able to bundle all >notifications for a single event class into one message. > >That would be very nice, if I would have only one event class. >However, I've got a whole bunch of different events happening in the >document management system. >For example: >- new document is added >- document is changed >- document is due to expire >and so on.. > >Our users can subscribe to any combination of these events, however, >they don't want to receice separate e-mails for every event type. They >don't want a mail which tells them which documents are added, and >another mail telling them which documents are changed. >Instead of separate mails, I need to be able to combine all >notifications into one single message. > >As far as I can see, there is no way I can change the way SQL NS >determines which notifications can be digested into one single >notification. > >This leaves me with 2 alternatives: >1) not using SQL NS >2) use SQL NS, and build a custom delivery protocol. >The custom delivery protocol would store all notifications into a >database table. >While the delivery protocol handles all subscriptions, notifications >for all event types are collected together in this database. >Following to that, I would run a "GROUP BY subscriber" query on this >table to get the notifications for each user. Finally, I would create >an e-mail message for each user, containing the grouped notifications, >send this message via SMTP, and clean up the table. > > >I don't like option 1), because it would cost us a lot to develop our >own system, and especially make it perform as well as SQL NS. >Option 2) isnt' that great either. > >So, are there any alternatives to solve this problem ? > >regards,
I also came up with that idea.
There are some events which I should be able to merge into one event class, with an extra parameter. This would work quite well for events like "document added" and "document changed", because their event classes are almost the same, and users subscribe to these events in the same way. However, some other document-related events are less suitable for merging. These events have other event fields and other subscription parameters. For example, users can receive a notification when the end of the publication term for a document approaches. In their subscription, they're able to set the number of days before the end date they want to be warned. Merging all event classes would be possible, but it would leave me with complicated event classes and lots of subscription parameters. In addition to that, users should still be able to subscribe to some of the merged events, and not all. This would result in another subscription parameter, which should hold all the events the user subscribed to. If there is no better solution, I'll try to work something out with merged event classes, but I was hoping there was a better alternative...
hi hilbert
assuming your using 2005, try bundling all your action specific parameters into one xml type event parameter so as to keep the event class simple and then use xquery or similar to pick out the values when you need to kate
Gotcha -
Here's a thread that may help with passing optional parameters. http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_thread/thread/1937323c4b8361fd/92bb28d6a17e2139?q=optional+&rnum=2#92bb28d6a17e2139 Joe -- Joe Webb SQL Server MVP http://www.sqlns.com ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 I support PASS, the Professional Association for SQL Server. (www.sqlpass.org) [quoted text, click to view] On 3 Mar 2006 03:58:42 -0800, "gotcha" <hleijen@gmail.com> wrote:
>I also came up with that idea. > >There are some events which I should be able to merge into one event >class, with an extra parameter. >This would work quite well for events like "document added" and >"document changed", because their event classes are almost the same, >and users subscribe to these events in the same way. > >However, some other document-related events are less suitable for >merging. >These events have other event fields and other subscription parameters. > >For example, users can receive a notification when the end of the >publication term for a document approaches. >In their subscription, they're able to set the number of days before >the end date they want to be warned. > >Merging all event classes would be possible, but it would leave me with >complicated event classes and lots of subscription parameters. >In addition to that, users should still be able to subscribe to some of >the merged events, and not all. >This would result in another subscription parameter, which should hold >all the events the user subscribed to. > >If there is no better solution, I'll try to work something out with >merged event classes, but I was hoping there was a better
kate,
unfortunately I can't target SQL 2005 because most of our customers are running SQL2000 at the moment and it's not very likely they're going to upgrade soon. so, no XML data type for me :-(
thnx Joe,
it looks like I'll have a lot of parameters for my subscriptions, or a lot of separate subscription classes. I'll try to work out some compromis which limits the number of subscription classes and still needs a limited amount of parameters.' I hope the performance of SQL NS will still be acceptable in our situation. I don't expect really large numbers of subscribers, however the queries for the matching rules are going to be quite complex, because I need a bunch of extra joins to check the document permissions for the subscribers.
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |