|
|
You're in the
sql server notification services
group:Suggested configuration?
sql server notification services:
Hi,
I need to build a notification system that will send notifications when a certain field changes in my database. Other data for the row may change, and that should not cause a notification to be sent. I'd also prefer not to create a column just to track when the field i'm interested in changes. My first though was to use chronicles and to schedule a subscription (I only want one notification sent to subscribers a day). Attempting this has been rather challenging. Now I'm considering having immediate notifications and events, and simply using a hosted provider that only runs once a day and compares the last view to the current value for all rows whose ModifiedDate is greater than the last time the provider ran. I think this will be quite a bit simpler, but I want to make sure I'm not violating any basic principles behind NS application design. Thoughts? Thanks Andy
Do you have Shyam Pather's book 'Microsoft SQL Server 2005 Notification
Services'? It has great example projects and source code, etc that demonstrate different NS principles. Your problem looks similar to the Subscription by Genre functionality in his Music Store sample. As songs get added to the Music Store inventory, they generate distinct events, but the Subscription by Genre rule only generates one notification per day. So if you threw a update trigger on your table, you could insert events into an event view whenever your special field changes, and then have a scheduled subscription that fires off a single notification per day no matter how many times the field changed. [quoted text, click to view] "Andy" <andyj@med-associates.com> wrote in message news:1175028657.056677.207230@b75g2000hsg.googlegroups.com... > Hi, > > I need to build a notification system that will send notifications > when a certain field changes in my database. Other data for the row > may change, and that should not cause a notification to be sent. I'd > also prefer not to create a column just to track when the field i'm > interested in changes. > > My first though was to use chronicles and to schedule a subscription > (I only want one notification sent to subscribers a day). Attempting > this has been rather challenging. > > Now I'm considering having immediate notifications and events, and > simply using a hosted provider that only runs once a day and compares > the last view to the current value for all rows whose ModifiedDate is > greater than the last time the provider ran. > > I think this will be quite a bit simpler, but I want to make sure I'm > not violating any basic principles behind NS application design. > > Thoughts? > Thanks > Andy >
[quoted text, click to view]
On Mar 27, 5:10 pm, "Ken" <k...@quitnet.com> wrote: > Do you have Shyam Pather's book 'Microsoft SQL Server 2005 Notification > Services'? > > It has great example projects and source code, etc that demonstrate > different NS principles. > > Your problem looks similar to the Subscription by Genre functionality in his > Music Store sample. As songs get added to the Music Store inventory, they > generate distinct events, but the Subscription by Genre rule only generates > one notification per day. > > So if you threw a update trigger on your table, you could insert events into > an event view whenever your special field changes, and then have a scheduled > subscription that fires off a single notification per day no matter how many > times the field changed. Ken, Yes, I have read that book. The problem with your suggestion is that if the field is changed back later that day, no notification should be sent at all for that row, but the trigger would cause two notifications. The resulting notification would be confusing, because it would instruct the subscriber to add and remove the same person.. That is the reason I was thinking of putting more logic into the event provider; the only time the events should occur is at the end of the day, not throughout the day. Andy
[quoted text, click to view]
On Mar 28, 11:01 am, "Ken" <k...@quitnet.com> wrote: > What if the event stored the value of the field at the time of the event, > and the trigger did something like: > > If there is an event the same day with a different field value > delete the event > else > insert an event > > then do the notifications once per day Hmm, I hadn't thought of that. Although it seems more complicated then just running the SqlProvider once a day.
[quoted text, click to view]
On Mar 28, 11:01 am, "Ken" <k...@quitnet.com> wrote: > What if the event stored the value of the field at the time of the event, > and the trigger did something like: > > If there is an event the same day with a different field value > delete the event > else > insert an event > > then do the notifications once per day Before I decide how to proceed, is there a reason that I shouldn't just go with the plan I suggested? Does it violate some basic NS principle that will cause me problems down the road?
[quoted text, click to view]
> Yes, I have read that book. The problem with your suggestion is that > if the field is changed back later that day, no notification should be > sent at all for that row, but the trigger would cause two > notifications. The resulting notification would be confusing, because > it would instruct the subscriber to add and remove the same person.. What if the event stored the value of the field at the time of the event, and the trigger did something like: If there is an event the same day with a different field value delete the event else insert an event then do the notifications once per day
[quoted text, click to view]
On Wed, 28 Mar 2007 15:02:10 -0400, Ken wrote: >> Before I decide how to proceed, is there a reason that I shouldn't >> just go with the plan I suggested? Does it violate some basic NS >> principle that will cause me problems down the road? > > Well I am certainly not an NS expert, so I can't speak with authority about > your solution, which seems fine to me.... Given what you need to accomplish, I'd say your plan would work. However, I typically try to not pin my solutions to what the specifications du jour are. In your case, making design decisions that limit the number of times throughout the day that the event provider can run may cause problems down the road when/if someone changes the requirements. So, I'd lean towards a chron table with insert/delete logic as Ken suggested. Just my thoughts, FWIW... Cheers! -- Joe Webb SQL Server MVP http://www.sqlns.com
[quoted text, click to view]
> Before I decide how to proceed, is there a reason that I shouldn't > just go with the plan I suggested? Does it violate some basic NS > principle that will cause me problems down the road? Well I am certainly not an NS expert, so I can't speak with authority about your solution, which seems fine to me....
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 |