|
|
You're in the
sql server notification services
group:ScheduleRecurrence/Start Questions
sql server notification services:
set time each day. But the ScheduledRule model as presented in NS BOL and examples doesn't work because it uses a model of a chronicle table periodically (presumably) populated by an EP in a manner disconnected from the ScheduledRule. But I don't want to periodically update and "send the most recent." If I don't include a reference to a document published at, say 6:54 am in the 7 am email then that is not correct. I need the scheduled rule and the population of data that will turn into Events to be coordinated. I could get around this (I think) if I could put all the work I need to do to populate Events into the ScheduledRule Action, but not all the data I need is in SQL Server, and I am wary of relying on a linked server in production because the call might move as many as n * 10^5 or n * 10^6 rows -- and needs to be designed to support this. So, is there another recommended approach for time-based notifications which need to trigger gathering Events and generating notifications in transaction, other than Scheduled Rule, or is that it? Also, can ScheduleRecurrence and ScheduleStart be set in the ADF or only programmatically from the .NET API?
Hi Mark -
I'm not sure I 100% understand why chronicles tables won't work for you. Can you give a more concrete example? As you consider one, perhaps this will help (or maybe I'm way off here). You can, in the ChronicleRule Action node, explicitly define what the Event Provide should do. Keeping track of the most recent is only one part of it. For instance, you can have it keep track of high watermarks for each of the items you're tracking (i.e. highest price of each stock reached during the day). Of course the same goes for low watermarks. Using Chronicles tables in this manner can prevent unwanted notifications. You can also have it chronicle an "as of" datetime if you'd like. Of course you can, if you'd like, look into ways to move the scheduling burden to the Event Provider. Under this scenario, you could call the event stored procedures of NS from a data database. You could schedule them as a job to occur as frequenly as needed. All of you subscriptions would be event-driven subscriptions rather than scheduled. Personally, I'm not a fan of this method, but it may be the best solution for your needs. BTW - .net framework is the only supported way to modify subscriptions. HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] MarkSW wrote:
> My requirements are for a set of Subscribers to recieve notifications at a > set time each day. But the ScheduledRule model as presented in NS BOL and > examples doesn't work because it uses a model of a chronicle table > periodically (presumably) populated by an EP in a manner disconnected from > the ScheduledRule. But I don't want to periodically update and "send the > most recent." If I don't include a reference to a document published at, say > 6:54 am in the 7 am email then that is not correct. I need the scheduled > rule and the population of data that will turn into Events to be coordinated. > > I could get around this (I think) if I could put all the work I need to do > to populate Events into the ScheduledRule Action, but not all the data I need > is in SQL Server, and I am wary of relying on a linked server in production > because the call might move as many as n * 10^5 or n * 10^6 rows -- and needs > to be designed to support this. > > So, is there another recommended approach for time-based notifications which > need to trigger gathering Events and generating notifications in transaction, > other than Scheduled Rule, or is that it? > > Also, can ScheduleRecurrence and ScheduleStart be set in the ADF or only > programmatically from the .NET API? >
OK, I will try again to explain why Chronicle model doesn't work.
Subscribers A, B and C sign up to recieve a notification every day at 7 a.m. This notification is a digest with links to every document published by Company X in the LAST 24 HOURS IMMEDIATELY PRIOR to that 7 a.m. start time. That is the requirement. So, that requirement cannot be met by a model where one "process" on a periodic schedule (the Event Chronicle updates) runs in a way that is not connected to the time subscription. Let's say the event gathering takes 3 minutes. Let's say we run it every five minutes all day (leaving aside the fact that I only need it updated for three daily runs and this is quite wasteful). Well, I still can't guarantee that at 7 am it ran recently enough to include all the docuemnts that were published right up until 7 am in that email. Because it is not in any way tied to the Scheduled Rule firing. So, it looks I need to define all the event gathering in the Action for the Subscription Rule, because that is what is tied to the time for that Subscription coming due. But that brings me back to not having all the data in SQL Server. Hope this is more clear. Please enlighten if I am misunderstanding how the Event Chronicle scheme works. This is not a model as in the examples where data is updated non-predictably but periodically and subscribers sign up for whatever time they want to "get the latest." That implies a less stringent "contract" with the customer. Beleive me, the first time a mail went out that didn't include a link to some important document published at 6:59, I would hear about it. [quoted text, click to view] "Joe Webb" wrote:
> Hi Mark - > > I'm not sure I 100% understand why chronicles tables won't work for you. > Can you give a more concrete example? As you consider one, perhaps this > will help (or maybe I'm way off here). > > You can, in the ChronicleRule Action node, explicitly define what the > Event Provide should do. Keeping track of the most recent is only one > part of it. For instance, you can have it keep track of high watermarks > for each of the items you're tracking (i.e. highest price of each stock > reached during the day). Of course the same goes for low watermarks. > Using Chronicles tables in this manner can prevent unwanted > notifications. You can also have it chronicle an "as of" datetime if > you'd like. > > Of course you can, if you'd like, look into ways to move the scheduling > burden to the Event Provider. Under this scenario, you could call the > event stored procedures of NS from a data database. You could schedule > them as a job to occur as frequenly as needed. All of you subscriptions > would be event-driven subscriptions rather than scheduled. Personally, > I'm not a fan of this method, but it may be the best solution for your > needs. > > BTW - .net framework is the only supported way to modify subscriptions. > > HTH... > Joe Webb > SQL Server MVP > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > MarkSW wrote: > > My requirements are for a set of Subscribers to recieve notifications at a > > set time each day. But the ScheduledRule model as presented in NS BOL and > > examples doesn't work because it uses a model of a chronicle table > > periodically (presumably) populated by an EP in a manner disconnected from > > the ScheduledRule. But I don't want to periodically update and "send the > > most recent." If I don't include a reference to a document published at, say > > 6:54 am in the 7 am email then that is not correct. I need the scheduled > > rule and the population of data that will turn into Events to be coordinated. > > > > I could get around this (I think) if I could put all the work I need to do > > to populate Events into the ScheduledRule Action, but not all the data I need > > is in SQL Server, and I am wary of relying on a linked server in production > > because the call might move as many as n * 10^5 or n * 10^6 rows -- and needs > > to be designed to support this. > > > > So, is there another recommended approach for time-based notifications which > > need to trigger gathering Events and generating notifications in transaction, > > other than Scheduled Rule, or is that it? > > > > Also, can ScheduleRecurrence and ScheduleStart be set in the ADF or only > > programmatically from the .NET API? > > > > Thanks all for your consideration and assistance ...
Joe, sorry for my dashed off and perhaps strident reply. I mean to add,
"Thanks for your reply, and any additional insight would be appreciated!" [quoted text, click to view] "MarkSW" wrote:
> OK, I will try again to explain why Chronicle model doesn't work. > > Subscribers A, B and C sign up to recieve a notification every day at 7 a.m. > This notification is a digest with links to every document published by > Company X in the LAST 24 HOURS IMMEDIATELY PRIOR to that 7 a.m. start time. > That is the requirement. So, that requirement cannot be met by a model where > one "process" on a periodic schedule (the Event Chronicle updates) runs in a > way that is not connected to the time subscription. > > Let's say the event gathering takes 3 minutes. Let's say we run it every > five minutes all day (leaving aside the fact that I only need it updated for > three daily runs and this is quite wasteful). Well, I still can't guarantee > that at 7 am it ran recently enough to include all the docuemnts that were > published right up until 7 am in that email. Because it is not in any way > tied to the Scheduled Rule firing. > > So, it looks I need to define all the event gathering in the Action for the > Subscription Rule, because that is what is tied to the time for that > Subscription coming due. But that brings me back to not having all the data > in SQL Server. > > Hope this is more clear. Please enlighten if I am misunderstanding how the > Event Chronicle scheme works. > > This is not a model as in the examples where data is updated non-predictably > but periodically and subscribers sign up for whatever time they want to "get > the latest." That implies a less stringent "contract" with the customer. > Beleive me, the first time a mail went out that didn't include a link to some > important document published at 6:59, I would hear about it. > > > "Joe Webb" wrote: > > > Hi Mark - > > > > I'm not sure I 100% understand why chronicles tables won't work for you. > > Can you give a more concrete example? As you consider one, perhaps this > > will help (or maybe I'm way off here). > > > > You can, in the ChronicleRule Action node, explicitly define what the > > Event Provide should do. Keeping track of the most recent is only one > > part of it. For instance, you can have it keep track of high watermarks > > for each of the items you're tracking (i.e. highest price of each stock > > reached during the day). Of course the same goes for low watermarks. > > Using Chronicles tables in this manner can prevent unwanted > > notifications. You can also have it chronicle an "as of" datetime if > > you'd like. > > > > Of course you can, if you'd like, look into ways to move the scheduling > > burden to the Event Provider. Under this scenario, you could call the > > event stored procedures of NS from a data database. You could schedule > > them as a job to occur as frequenly as needed. All of you subscriptions > > would be event-driven subscriptions rather than scheduled. Personally, > > I'm not a fan of this method, but it may be the best solution for your > > needs. > > > > BTW - .net framework is the only supported way to modify subscriptions. > > > > HTH... > > Joe Webb > > SQL Server MVP > > > > ~~~ > > Get up to speed quickly with SQLNS > > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > > > > > MarkSW wrote: > > > My requirements are for a set of Subscribers to recieve notifications at a > > > set time each day. But the ScheduledRule model as presented in NS BOL and > > > examples doesn't work because it uses a model of a chronicle table > > > periodically (presumably) populated by an EP in a manner disconnected from > > > the ScheduledRule. But I don't want to periodically update and "send the > > > most recent." If I don't include a reference to a document published at, say > > > 6:54 am in the 7 am email then that is not correct. I need the scheduled > > > rule and the population of data that will turn into Events to be coordinated. > > > > > > I could get around this (I think) if I could put all the work I need to do > > > to populate Events into the ScheduledRule Action, but not all the data I need > > > is in SQL Server, and I am wary of relying on a linked server in production > > > because the call might move as many as n * 10^5 or n * 10^6 rows -- and needs > > > to be designed to support this. > > > > > > So, is there another recommended approach for time-based notifications which > > > need to trigger gathering Events and generating notifications in transaction, > > > other than Scheduled Rule, or is that it? > > > > > > Also, can ScheduleRecurrence and ScheduleStart be set in the ADF or only > > > programmatically from the .NET API? > > > > > > Thanks all for your consideration and assistance ...
:) No problem!! :)
HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] MarkSW wrote:
> Joe, sorry for my dashed off and perhaps strident reply. I mean to add, > "Thanks for your reply, and any additional insight would be appreciated!" > > "MarkSW" wrote: > > >>OK, I will try again to explain why Chronicle model doesn't work. >> >>Subscribers A, B and C sign up to recieve a notification every day at 7 a.m. >> This notification is a digest with links to every document published by >>Company X in the LAST 24 HOURS IMMEDIATELY PRIOR to that 7 a.m. start time. >>That is the requirement. So, that requirement cannot be met by a model where >>one "process" on a periodic schedule (the Event Chronicle updates) runs in a >>way that is not connected to the time subscription. >> >>Let's say the event gathering takes 3 minutes. Let's say we run it every >>five minutes all day (leaving aside the fact that I only need it updated for >>three daily runs and this is quite wasteful). Well, I still can't guarantee >>that at 7 am it ran recently enough to include all the docuemnts that were >>published right up until 7 am in that email. Because it is not in any way >>tied to the Scheduled Rule firing. >> >>So, it looks I need to define all the event gathering in the Action for the >>Subscription Rule, because that is what is tied to the time for that >>Subscription coming due. But that brings me back to not having all the data >>in SQL Server. >> >>Hope this is more clear. Please enlighten if I am misunderstanding how the >>Event Chronicle scheme works. >> >>This is not a model as in the examples where data is updated non-predictably >>but periodically and subscribers sign up for whatever time they want to "get >>the latest." That implies a less stringent "contract" with the customer. >>Beleive me, the first time a mail went out that didn't include a link to some >>important document published at 6:59, I would hear about it. >> >> >>"Joe Webb" wrote: >> >> >>>Hi Mark - >>> >>>I'm not sure I 100% understand why chronicles tables won't work for you. >>>Can you give a more concrete example? As you consider one, perhaps this >>>will help (or maybe I'm way off here). >>> >>>You can, in the ChronicleRule Action node, explicitly define what the >>>Event Provide should do. Keeping track of the most recent is only one >>>part of it. For instance, you can have it keep track of high watermarks >>>for each of the items you're tracking (i.e. highest price of each stock >>>reached during the day). Of course the same goes for low watermarks. >>>Using Chronicles tables in this manner can prevent unwanted >>>notifications. You can also have it chronicle an "as of" datetime if >>>you'd like. >>> >>>Of course you can, if you'd like, look into ways to move the scheduling >>>burden to the Event Provider. Under this scenario, you could call the >>>event stored procedures of NS from a data database. You could schedule >>>them as a job to occur as frequenly as needed. All of you subscriptions >>>would be event-driven subscriptions rather than scheduled. Personally, >>>I'm not a fan of this method, but it may be the best solution for your >>>needs. >>> >>>BTW - .net framework is the only supported way to modify subscriptions. >>> >>>HTH... >>>Joe Webb >>>SQL Server MVP >>> >>>~~~ >>>Get up to speed quickly with SQLNS >>>http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 >>> >>> >>> >>>MarkSW wrote: >>> >>>>My requirements are for a set of Subscribers to recieve notifications at a >>>>set time each day. But the ScheduledRule model as presented in NS BOL and >>>>examples doesn't work because it uses a model of a chronicle table >>>>periodically (presumably) populated by an EP in a manner disconnected from >>>>the ScheduledRule. But I don't want to periodically update and "send the >>>>most recent." If I don't include a reference to a document published at, say >>>>6:54 am in the 7 am email then that is not correct. I need the scheduled >>>>rule and the population of data that will turn into Events to be coordinated. >>>> >>>>I could get around this (I think) if I could put all the work I need to do >>>>to populate Events into the ScheduledRule Action, but not all the data I need >>>>is in SQL Server, and I am wary of relying on a linked server in production >>>>because the call might move as many as n * 10^5 or n * 10^6 rows -- and needs >>>>to be designed to support this. >>>> >>>>So, is there another recommended approach for time-based notifications which >>>>need to trigger gathering Events and generating notifications in transaction, >>>>other than Scheduled Rule, or is that it? >>>> >>>>Also, can ScheduleRecurrence and ScheduleStart be set in the ADF or only >>>>programmatically from the .NET API? >>>> >>>>Thanks all for your consideration and assistance ...
Mark -
Okay, I think I better understand now. Thanks for the additional info. A couple of possibilities come to mind. Perhaps one will meet your needs. 1) Can you simply have the event provider update the chronicle table when a new document has been published for a certain company. Something like: INSERT INTO MyChron(CompanyId, DocDate) SELECT e.CompanyId, e.DocDate FROM DocUpdates e WHERE e.CompanyId NOT IN (SELECT MyChron.CompanyId from MyChron) This does not capture how many documents have been published, just that at least one document has been published for the company. Then during the scheduled rule firing you go out and get a list of all the published documents for the companies that the subscriber is interested in. If a company publishes a new doc at 6:59:45, the new will make it. If the new doc is the first document of the day for the company, well it *should* make it, but you'll want to triple check it. 2) Another way may be to use the sql ns event provider stored procedures , calling them from the data database. When a new doc is published in the data database a sproc or trigger is fired that calls the ns event provider sprocs in the ns application database and submits the event (including all information about the publication) to the ns application. Again, this *should* work, but you'll of course want to triple check it. NSEventBeginBatch<EventClassName> NSEventSubmitBatch<EventClassName> NSEventFlushBatch<EventClassName> NSEventWrite<EventClassName> HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] MarkSW wrote:
> OK, I will try again to explain why Chronicle model doesn't work. > > Subscribers A, B and C sign up to recieve a notification every day at 7 a.m. > This notification is a digest with links to every document published by > Company X in the LAST 24 HOURS IMMEDIATELY PRIOR to that 7 a.m. start time. > That is the requirement. So, that requirement cannot be met by a model where > one "process" on a periodic schedule (the Event Chronicle updates) runs in a > way that is not connected to the time subscription. > > Let's say the event gathering takes 3 minutes. Let's say we run it every > five minutes all day (leaving aside the fact that I only need it updated for > three daily runs and this is quite wasteful). Well, I still can't guarantee > that at 7 am it ran recently enough to include all the docuemnts that were > published right up until 7 am in that email. Because it is not in any way > tied to the Scheduled Rule firing. > > So, it looks I need to define all the event gathering in the Action for the > Subscription Rule, because that is what is tied to the time for that > Subscription coming due. But that brings me back to not having all the data > in SQL Server. > > Hope this is more clear. Please enlighten if I am misunderstanding how the > Event Chronicle scheme works. > > This is not a model as in the examples where data is updated non-predictably > but periodically and subscribers sign up for whatever time they want to "get > the latest." That implies a less stringent "contract" with the customer. > Beleive me, the first time a mail went out that didn't include a link to some > important document published at 6:59, I would hear about it. > > > "Joe Webb" wrote: > > >>Hi Mark - >> >>I'm not sure I 100% understand why chronicles tables won't work for you. >>Can you give a more concrete example? As you consider one, perhaps this >>will help (or maybe I'm way off here). >> >>You can, in the ChronicleRule Action node, explicitly define what the >>Event Provide should do. Keeping track of the most recent is only one >>part of it. For instance, you can have it keep track of high watermarks >>for each of the items you're tracking (i.e. highest price of each stock >>reached during the day). Of course the same goes for low watermarks. >>Using Chronicles tables in this manner can prevent unwanted >>notifications. You can also have it chronicle an "as of" datetime if >>you'd like. >> >>Of course you can, if you'd like, look into ways to move the scheduling >>burden to the Event Provider. Under this scenario, you could call the >>event stored procedures of NS from a data database. You could schedule >>them as a job to occur as frequenly as needed. All of you subscriptions >>would be event-driven subscriptions rather than scheduled. Personally, >>I'm not a fan of this method, but it may be the best solution for your >>needs. >> >>BTW - .net framework is the only supported way to modify subscriptions. >> >>HTH... >>Joe Webb >>SQL Server MVP >> >>~~~ >>Get up to speed quickly with SQLNS >>http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 >> >> >> >>MarkSW wrote: >> >>>My requirements are for a set of Subscribers to recieve notifications at a >>>set time each day. But the ScheduledRule model as presented in NS BOL and >>>examples doesn't work because it uses a model of a chronicle table >>>periodically (presumably) populated by an EP in a manner disconnected from >>>the ScheduledRule. But I don't want to periodically update and "send the >>>most recent." If I don't include a reference to a document published at, say >>>6:54 am in the 7 am email then that is not correct. I need the scheduled >>>rule and the population of data that will turn into Events to be coordinated. >>> >>>I could get around this (I think) if I could put all the work I need to do >>>to populate Events into the ScheduledRule Action, but not all the data I need >>>is in SQL Server, and I am wary of relying on a linked server in production >>>because the call might move as many as n * 10^5 or n * 10^6 rows -- and needs >>>to be designed to support this. >>> >>>So, is there another recommended approach for time-based notifications which >>>need to trigger gathering Events and generating notifications in transaction, >>>other than Scheduled Rule, or is that it? >>> >>>Also, can ScheduleRecurrence and ScheduleStart be set in the ADF or only >>>programmatically from the .NET API? >>> >>>Thanks all for your consideration and assistance ...
Oops...the INSERT statement in my earlier posting should of course
include an evaluation of the datetime column, but I thing you get the idea. HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] Joe Webb wrote:
> Mark - > > Okay, I think I better understand now. Thanks for the additional info. > > A couple of possibilities come to mind. Perhaps one will meet your needs. > > 1) Can you simply have the event provider update the chronicle table > when a new document has been published for a certain company. Something > like: > > INSERT INTO MyChron(CompanyId, DocDate) > SELECT e.CompanyId, e.DocDate > FROM DocUpdates e > WHERE e.CompanyId NOT IN (SELECT MyChron.CompanyId from MyChron) > > This does not capture how many documents have been published, just that > at least one document has been published for the company. Then during > the scheduled rule firing you go out and get a list of all the published > documents for the companies that the subscriber is interested in. > > If a company publishes a new doc at 6:59:45, the new will make it. If > the new doc is the first document of the day for the company, well it > *should* make it, but you'll want to triple check it. > > 2) Another way may be to use the sql ns event provider stored procedures > , calling them from the data database. When a new doc is published in > the data database a sproc or trigger is fired that calls the ns event > provider sprocs in the ns application database and submits the event > (including all information about the publication) to the ns application. > Again, this *should* work, but you'll of course want to triple check it. > > NSEventBeginBatch<EventClassName> > NSEventSubmitBatch<EventClassName> > NSEventFlushBatch<EventClassName> > NSEventWrite<EventClassName> > > > > HTH... > Joe Webb > SQL Server MVP > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > > MarkSW wrote: > >> OK, I will try again to explain why Chronicle model doesn't work. >> >> Subscribers A, B and C sign up to recieve a notification every day at >> 7 a.m. This notification is a digest with links to every document >> published by Company X in the LAST 24 HOURS IMMEDIATELY PRIOR to that >> 7 a.m. start time. That is the requirement. So, that requirement >> cannot be met by a model where one "process" on a periodic schedule >> (the Event Chronicle updates) runs in a way that is not connected to >> the time subscription. >> >> Let's say the event gathering takes 3 minutes. Let's say we run it >> every five minutes all day (leaving aside the fact that I only need it >> updated for three daily runs and this is quite wasteful). Well, I >> still can't guarantee that at 7 am it ran recently enough to include >> all the docuemnts that were published right up until 7 am in that >> email. Because it is not in any way tied to the Scheduled Rule firing. >> >> So, it looks I need to define all the event gathering in the Action >> for the Subscription Rule, because that is what is tied to the time >> for that Subscription coming due. But that brings me back to not >> having all the data in SQL Server. >> >> Hope this is more clear. Please enlighten if I am misunderstanding >> how the Event Chronicle scheme works. >> >> This is not a model as in the examples where data is updated >> non-predictably but periodically and subscribers sign up for whatever >> time they want to "get the latest." That implies a less stringent >> "contract" with the customer. Beleive me, the first time a mail went >> out that didn't include a link to some important document published at >> 6:59, I would hear about it. >> >> >> "Joe Webb" wrote: >> >> >>> Hi Mark - >>> >>> I'm not sure I 100% understand why chronicles tables won't work for >>> you. Can you give a more concrete example? As you consider one, >>> perhaps this will help (or maybe I'm way off here). >>> >>> You can, in the ChronicleRule Action node, explicitly define what the >>> Event Provide should do. Keeping track of the most recent is only one >>> part of it. For instance, you can have it keep track of high >>> watermarks for each of the items you're tracking (i.e. highest price >>> of each stock reached during the day). Of course the same goes for >>> low watermarks. Using Chronicles tables in this manner can prevent >>> unwanted notifications. You can also have it chronicle an "as of" >>> datetime if you'd like. >>> >>> Of course you can, if you'd like, look into ways to move the >>> scheduling burden to the Event Provider. Under this scenario, you >>> could call the event stored procedures of NS from a data database. >>> You could schedule them as a job to occur as frequenly as needed. All >>> of you subscriptions would be event-driven subscriptions rather than >>> scheduled. Personally, I'm not a fan of this method, but it may be >>> the best solution for your needs. >>> >>> BTW - .net framework is the only supported way to modify subscriptions. >>> >>> HTH... >>> Joe Webb >>> SQL Server MVP >>> >>> ~~~ >>> Get up to speed quickly with SQLNS >>> http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 >>> >>> >>> >>> MarkSW wrote: >>> >>>> My requirements are for a set of Subscribers to recieve >>>> notifications at a set time each day. But the ScheduledRule model >>>> as presented in NS BOL and examples doesn't work because it uses a >>>> model of a chronicle table periodically (presumably) populated by an >>>> EP in a manner disconnected from the ScheduledRule. But I don't >>>> want to periodically update and "send the most recent." If I don't >>>> include a reference to a document published at, say 6:54 am in the 7 >>>> am email then that is not correct. I need the scheduled rule and >>>> the population of data that will turn into Events to be coordinated. >>>> >>>> I could get around this (I think) if I could put all the work I need >>>> to do to populate Events into the ScheduledRule Action, but not all >>>> the data I need is in SQL Server, and I am wary of relying on a >>>> linked server in production because the call might move as many as n >>>> * 10^5 or n * 10^6 rows -- and needs to be designed to support this. >>>> >>>> So, is there another recommended approach for time-based >>>> notifications which need to trigger gathering Events and generating >>>> notifications in transaction, other than Scheduled Rule, or is that it? >>>> >>>> Also, can ScheduleRecurrence and ScheduleStart be set in the ADF or >>>> only programmatically from the .NET API? >>>> >>>> Thanks all for your consideration and assistance ... >>>
Thanks for the replies.
I have been thinking about something along the lines of the second approach, but your idea is more complete and focused. Perpaps a trigger could be designed to do the Insert directly from the target table making the NS calls. The issue would be performance since this is probably the fundamental table in our schema. The first approach seems nominally the same idea but in application code, which means I don't like it as much simply because I'd rather let the database do the work for me ... [quoted text, click to view] "Joe Webb" wrote:
> Mark - > > Okay, I think I better understand now. Thanks for the additional info. > > A couple of possibilities come to mind. Perhaps one will meet your needs. > > 1) Can you simply have the event provider update the chronicle table > when a new document has been published for a certain company. Something > like: > > INSERT INTO MyChron(CompanyId, DocDate) > SELECT e.CompanyId, e.DocDate > FROM DocUpdates e > WHERE e.CompanyId NOT IN (SELECT MyChron.CompanyId from MyChron) > > This does not capture how many documents have been published, just that > at least one document has been published for the company. Then during > the scheduled rule firing you go out and get a list of all the published > documents for the companies that the subscriber is interested in. > > If a company publishes a new doc at 6:59:45, the new will make it. If > the new doc is the first document of the day for the company, well it > *should* make it, but you'll want to triple check it. > > 2) Another way may be to use the sql ns event provider stored procedures > , calling them from the data database. When a new doc is published in > the data database a sproc or trigger is fired that calls the ns event > provider sprocs in the ns application database and submits the event > (including all information about the publication) to the ns application. > Again, this *should* work, but you'll of course want to triple check it. > > NSEventBeginBatch<EventClassName> > NSEventSubmitBatch<EventClassName> > NSEventFlushBatch<EventClassName> > NSEventWrite<EventClassName> > > > > HTH... > Joe Webb > SQL Server MVP > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > > MarkSW wrote: > > OK, I will try again to explain why Chronicle model doesn't work. > > > > Subscribers A, B and C sign up to recieve a notification every day at 7 a.m. > > This notification is a digest with links to every document published by > > Company X in the LAST 24 HOURS IMMEDIATELY PRIOR to that 7 a.m. start time. > > That is the requirement. So, that requirement cannot be met by a model where > > one "process" on a periodic schedule (the Event Chronicle updates) runs in a > > way that is not connected to the time subscription. > > > > Let's say the event gathering takes 3 minutes. Let's say we run it every > > five minutes all day (leaving aside the fact that I only need it updated for > > three daily runs and this is quite wasteful). Well, I still can't guarantee > > that at 7 am it ran recently enough to include all the docuemnts that were > > published right up until 7 am in that email. Because it is not in any way > > tied to the Scheduled Rule firing. > > > > So, it looks I need to define all the event gathering in the Action for the > > Subscription Rule, because that is what is tied to the time for that > > Subscription coming due. But that brings me back to not having all the data > > in SQL Server. > > > > Hope this is more clear. Please enlighten if I am misunderstanding how the > > Event Chronicle scheme works. > > > > This is not a model as in the examples where data is updated non-predictably > > but periodically and subscribers sign up for whatever time they want to "get > > the latest." That implies a less stringent "contract" with the customer. > > Beleive me, the first time a mail went out that didn't include a link to some > > important document published at 6:59, I would hear about it. > > > > > > "Joe Webb" wrote: > > > > > >>Hi Mark - > >> > >>I'm not sure I 100% understand why chronicles tables won't work for you. > >>Can you give a more concrete example? As you consider one, perhaps this > >>will help (or maybe I'm way off here). > >> > >>You can, in the ChronicleRule Action node, explicitly define what the > >>Event Provide should do. Keeping track of the most recent is only one > >>part of it. For instance, you can have it keep track of high watermarks > >>for each of the items you're tracking (i.e. highest price of each stock > >>reached during the day). Of course the same goes for low watermarks. > >>Using Chronicles tables in this manner can prevent unwanted > >>notifications. You can also have it chronicle an "as of" datetime if > >>you'd like. > >> > >>Of course you can, if you'd like, look into ways to move the scheduling > >>burden to the Event Provider. Under this scenario, you could call the > >>event stored procedures of NS from a data database. You could schedule > >>them as a job to occur as frequenly as needed. All of you subscriptions > >>would be event-driven subscriptions rather than scheduled. Personally, > >>I'm not a fan of this method, but it may be the best solution for your > >>needs. > >> > >>BTW - .net framework is the only supported way to modify subscriptions. > >> > >>HTH... > >>Joe Webb > >>SQL Server MVP > >> > >>~~~ > >>Get up to speed quickly with SQLNS > >>http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > >> > >> > >> > >>MarkSW wrote: > >> > >>>My requirements are for a set of Subscribers to recieve notifications at a > >>>set time each day. But the ScheduledRule model as presented in NS BOL and > >>>examples doesn't work because it uses a model of a chronicle table > >>>periodically (presumably) populated by an EP in a manner disconnected from > >>>the ScheduledRule. But I don't want to periodically update and "send the > >>>most recent." If I don't include a reference to a document published at, say > >>>6:54 am in the 7 am email then that is not correct. I need the scheduled > >>>rule and the population of data that will turn into Events to be coordinated. > >>> > >>>I could get around this (I think) if I could put all the work I need to do > >>>to populate Events into the ScheduledRule Action, but not all the data I need > >>>is in SQL Server, and I am wary of relying on a linked server in production > >>>because the call might move as many as n * 10^5 or n * 10^6 rows -- and needs > >>>to be designed to support this. > >>> > >>>So, is there another recommended approach for time-based notifications which > >>>need to trigger gathering Events and generating notifications in transaction,
Hi Mark -
Both of these are really database solutions; in the first case it's the data database that does the work and in the latter case it's the NS database that does the work of gathering all of the related info for the notification. Still it sounds likes #1 may be the better alternative for your scenario. Would you post whatever solution you choose and let us know how it works out for you? HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] MarkSW wrote:
> Thanks for the replies. > > I have been thinking about something along the lines of the second approach, > but your idea is more complete and focused. Perpaps a trigger could be > designed to do the Insert directly from the target table making the NS calls. > The issue would be performance since this is probably the fundamental table > in our schema. > > The first approach seems nominally the same idea but in application code, > which means I don't like it as much simply because I'd rather let the > database do the work for me ... > > > "Joe Webb" wrote: > > >>Mark - >> >>Okay, I think I better understand now. Thanks for the additional info. >> >>A couple of possibilities come to mind. Perhaps one will meet your needs. >> >>1) Can you simply have the event provider update the chronicle table >>when a new document has been published for a certain company. Something >>like: >> >>INSERT INTO MyChron(CompanyId, DocDate) >>SELECT e.CompanyId, e.DocDate >>FROM DocUpdates e >>WHERE e.CompanyId NOT IN (SELECT MyChron.CompanyId from MyChron) >> >>This does not capture how many documents have been published, just that >>at least one document has been published for the company. Then during >>the scheduled rule firing you go out and get a list of all the published >>documents for the companies that the subscriber is interested in. >> >>If a company publishes a new doc at 6:59:45, the new will make it. If >>the new doc is the first document of the day for the company, well it >>*should* make it, but you'll want to triple check it. >> >>2) Another way may be to use the sql ns event provider stored procedures >>, calling them from the data database. When a new doc is published in >>the data database a sproc or trigger is fired that calls the ns event >>provider sprocs in the ns application database and submits the event >>(including all information about the publication) to the ns application. >>Again, this *should* work, but you'll of course want to triple check it. >> >>NSEventBeginBatch<EventClassName> >>NSEventSubmitBatch<EventClassName> >>NSEventFlushBatch<EventClassName> >>NSEventWrite<EventClassName> >> >> >> >>HTH... >>Joe Webb >>SQL Server MVP >> >>~~~ >>Get up to speed quickly with SQLNS >>http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 >> >> >> >> >>MarkSW wrote: >> >>>OK, I will try again to explain why Chronicle model doesn't work. >>> >>>Subscribers A, B and C sign up to recieve a notification every day at 7 a.m. >>> This notification is a digest with links to every document published by >>>Company X in the LAST 24 HOURS IMMEDIATELY PRIOR to that 7 a.m. start time. >>>That is the requirement. So, that requirement cannot be met by a model where >>>one "process" on a periodic schedule (the Event Chronicle updates) runs in a >>>way that is not connected to the time subscription. >>> >>>Let's say the event gathering takes 3 minutes. Let's say we run it every >>>five minutes all day (leaving aside the fact that I only need it updated for >>>three daily runs and this is quite wasteful). Well, I still can't guarantee >>>that at 7 am it ran recently enough to include all the docuemnts that were >>>published right up until 7 am in that email. Because it is not in any way >>>tied to the Scheduled Rule firing. >>> >>>So, it looks I need to define all the event gathering in the Action for the >>>Subscription Rule, because that is what is tied to the time for that >>>Subscription coming due. But that brings me back to not having all the data >>>in SQL Server. >>> >>>Hope this is more clear. Please enlighten if I am misunderstanding how the >>>Event Chronicle scheme works. >>> >>>This is not a model as in the examples where data is updated non-predictably >>>but periodically and subscribers sign up for whatever time they want to "get >>>the latest." That implies a less stringent "contract" with the customer. >>>Beleive me, the first time a mail went out that didn't include a link to some >>>important document published at 6:59, I would hear about it. >>> >>> >>>"Joe Webb" wrote: >>> >>> >>> >>>>Hi Mark - >>>> >>>>I'm not sure I 100% understand why chronicles tables won't work for you. >>>>Can you give a more concrete example? As you consider one, perhaps this >>>>will help (or maybe I'm way off here). >>>> >>>>You can, in the ChronicleRule Action node, explicitly define what the >>>>Event Provide should do. Keeping track of the most recent is only one >>>>part of it. For instance, you can have it keep track of high watermarks >>>>for each of the items you're tracking (i.e. highest price of each stock >>>>reached during the day). Of course the same goes for low watermarks. >>>>Using Chronicles tables in this manner can prevent unwanted >>>>notifications. You can also have it chronicle an "as of" datetime if >>>>you'd like. >>>> >>>>Of course you can, if you'd like, look into ways to move the scheduling >>>>burden to the Event Provider. Under this scenario, you could call the >>>>event stored procedures of NS from a data database. You could schedule >>>>them as a job to occur as frequenly as needed. All of you subscriptions >>>>would be event-driven subscriptions rather than scheduled. Personally, >>>>I'm not a fan of this method, but it may be the best solution for your >>>>needs. >>>> >>>>BTW - .net framework is the only supported way to modify subscriptions. >>>> >>>>HTH... >>>>Joe Webb >>>>SQL Server MVP >>>> >>>>~~~ >>>>Get up to speed quickly with SQLNS >>>>http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 >>>> >>>> >>>> >>>>MarkSW wrote: >>>> >>>> >>>>>My requirements are for a set of Subscribers to recieve notifications at a >>>>>set time each day. But the ScheduledRule model as presented in NS BOL and >>>>>examples doesn't work because it uses a model of a chronicle table >>>>>periodically (presumably) populated by an EP in a manner disconnected from >>>>>the ScheduledRule. But I don't want to periodically update and "send the >>>>>most recent." If I don't include a reference to a document published at, say >>>>>6:54 am in the 7 am email then that is not correct. I need the scheduled >>>>>rule and the population of data that will turn into Events to be coordinated. >>>>>
Yes, I'm in the middle of this now. I'll post hopefully by the end of the
week, when it's (hopefully) done. [quoted text, click to view] "Joe Webb" wrote:
> Hi Mark - > > Both of these are really database solutions; in the first case it's the > data database that does the work and in the latter case it's the NS > database that does the work of gathering all of the related info for the > notification. > > Still it sounds likes #1 may be the better alternative for your > scenario. Would you post whatever solution you choose and let us know > how it works out for you? > > > HTH... > Joe Webb > SQL Server MVP > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > MarkSW wrote: > > Thanks for the replies. > > > > I have been thinking about something along the lines of the second approach, > > but your idea is more complete and focused. Perpaps a trigger could be > > designed to do the Insert directly from the target table making the NS calls. > > The issue would be performance since this is probably the fundamental table > > in our schema. > > > > The first approach seems nominally the same idea but in application code, > > which means I don't like it as much simply because I'd rather let the > > database do the work for me ... > > > > > > "Joe Webb" wrote: > > > > > >>Mark - > >> > >>Okay, I think I better understand now. Thanks for the additional info. > >> > >>A couple of possibilities come to mind. Perhaps one will meet your needs. > >> > >>1) Can you simply have the event provider update the chronicle table > >>when a new document has been published for a certain company. Something > >>like: > >> > >>INSERT INTO MyChron(CompanyId, DocDate) > >>SELECT e.CompanyId, e.DocDate > >>FROM DocUpdates e > >>WHERE e.CompanyId NOT IN (SELECT MyChron.CompanyId from MyChron) > >> > >>This does not capture how many documents have been published, just that > >>at least one document has been published for the company. Then during > >>the scheduled rule firing you go out and get a list of all the published > >>documents for the companies that the subscriber is interested in. > >> > >>If a company publishes a new doc at 6:59:45, the new will make it. If > >>the new doc is the first document of the day for the company, well it > >>*should* make it, but you'll want to triple check it. > >> > >>2) Another way may be to use the sql ns event provider stored procedures > >>, calling them from the data database. When a new doc is published in > >>the data database a sproc or trigger is fired that calls the ns event > >>provider sprocs in the ns application database and submits the event > >>(including all information about the publication) to the ns application. > >>Again, this *should* work, but you'll of course want to triple check it. > >> > >>NSEventBeginBatch<EventClassName> > >>NSEventSubmitBatch<EventClassName> > >>NSEventFlushBatch<EventClassName> > >>NSEventWrite<EventClassName> > >> > >> > >> > >>HTH... > >>Joe Webb > >>SQL Server MVP > >> > >>~~~ > >>Get up to speed quickly with SQLNS > >>http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > >> > >> > >> > >> > >>MarkSW wrote: > >> > >>>OK, I will try again to explain why Chronicle model doesn't work. > >>> > >>>Subscribers A, B and C sign up to recieve a notification every day at 7 a.m. > >>> This notification is a digest with links to every document published by > >>>Company X in the LAST 24 HOURS IMMEDIATELY PRIOR to that 7 a.m. start time. > >>>That is the requirement. So, that requirement cannot be met by a model where > >>>one "process" on a periodic schedule (the Event Chronicle updates) runs in a > >>>way that is not connected to the time subscription. > >>> > >>>Let's say the event gathering takes 3 minutes. Let's say we run it every > >>>five minutes all day (leaving aside the fact that I only need it updated for > >>>three daily runs and this is quite wasteful). Well, I still can't guarantee > >>>that at 7 am it ran recently enough to include all the docuemnts that were > >>>published right up until 7 am in that email. Because it is not in any way > >>>tied to the Scheduled Rule firing. > >>> > >>>So, it looks I need to define all the event gathering in the Action for the > >>>Subscription Rule, because that is what is tied to the time for that > >>>Subscription coming due. But that brings me back to not having all the data > >>>in SQL Server. > >>> > >>>Hope this is more clear. Please enlighten if I am misunderstanding how the > >>>Event Chronicle scheme works. > >>> > >>>This is not a model as in the examples where data is updated non-predictably > >>>but periodically and subscribers sign up for whatever time they want to "get > >>>the latest." That implies a less stringent "contract" with the customer. > >>>Beleive me, the first time a mail went out that didn't include a link to some > >>>important document published at 6:59, I would hear about it. > >>> > >>> > >>>"Joe Webb" wrote: > >>> > >>> > >>> > >>>>Hi Mark - > >>>> > >>>>I'm not sure I 100% understand why chronicles tables won't work for you. > >>>>Can you give a more concrete example? As you consider one, perhaps this > >>>>will help (or maybe I'm way off here). > >>>> > >>>>You can, in the ChronicleRule Action node, explicitly define what the > >>>>Event Provide should do. Keeping track of the most recent is only one > >>>>part of it. For instance, you can have it keep track of high watermarks > >>>>for each of the items you're tracking (i.e. highest price of each stock > >>>>reached during the day). Of course the same goes for low watermarks. > >>>>Using Chronicles tables in this manner can prevent unwanted > >>>>notifications. You can also have it chronicle an "as of" datetime if > >>>>you'd like. > >>>> > >>>>Of course you can, if you'd like, look into ways to move the scheduling > >>>>burden to the Event Provider. Under this scenario, you could call the > >>>>event stored procedures of NS from a data database. You could schedule > >>>>them as a job to occur as frequenly as needed. All of you subscriptions > >>>>would be event-driven subscriptions rather than scheduled. Personally, > >>>>I'm not a fan of this method, but it may be the best solution for your > >>>>needs. > >>>> > >>>>BTW - .net framework is the only supported way to modify subscriptions. > >>>> > >>>>HTH... > >>>>Joe Webb > >>>>SQL Server MVP > >>>> > >>>>~~~ > >>>>Get up to speed quickly with SQLNS > >>>>http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > >>>> > >>>> > >>>> > >>>>MarkSW wrote: > >>>> > >>>> > >>>>>My requirements are for a set of Subscribers to recieve notifications at a > >>>>>set time each day. But the ScheduledRule model as presented in NS BOL and
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 |