Hi,
I have some data in my database which stores when a contract expires.
I'd like to send a notification 90, 60 and 30 days before the contract
will expire.
I'm having trouble coming up with a good event to trigger
notifications. Do I setup the event class just to run once a month?
A custom event provider that fires once a month?
I'm sure I'm missing something simple..
Thanks
Andy
Ok, that's one of the ideas I had, but I'm glad you confirmed thats a valid way to proceed. I'll look at implementing the logic through the event provider. Thanks! Andy [quoted text, click to view] On Feb 12, 8:55 am, Joe Webb <j...@webbtechsolutions.com> wrote: > I'd probably configure the SQL Server Event Provider to query the database > once per day, looking for contract expirations that occur on the intervals > defined. > > HTH... > > -- > Joe Webb > SQL Server MVP http://www.sqlns.com
[quoted text, click to view] On Fri, 09 Feb 2007 11:44:20 -0800, Andy wrote:
> Hi,
>
> I have some data in my database which stores when a contract expires.
> I'd like to send a notification 90, 60 and 30 days before the contract
> will expire.
>
> I'm having trouble coming up with a good event to trigger
> notifications. Do I setup the event class just to run once a month?
> A custom event provider that fires once a month?
>
> I'm sure I'm missing something simple..
>
> Thanks
> Andy
I'd probably configure the SQL Server Event Provider to query the database once per day, looking for contract expirations that occur on the intervals defined. HTH... -- Joe Webb SQL Server MVP http://www.sqlns.com
[quoted text, click to view] On Feb 12, 8:55 am, Joe Webb <j...@webbtechsolutions.com> wrote:
> I'd probably configure the SQL Server Event Provider to query the database
> once per day, looking for contract expirations that occur on the intervals
> defined.
Joe, It looks like I can schedule the provider to run once a month. Would that be a valid approach? If I go the 'once a day' route, would I only include those that expire EXACTLY 90 days from now? Thanks Andy
I'm certainly no expert in NS, but had a thought that might help: Why not set up a chronicle table with thee extra fields (DateTime) in it for 90, 60, and 30 days. Have a Scheduled subscription that runs once per day. When the subscription runs, it looks for any records in the (over 60 but under 90) day range and updates the appropriate field in the chron table for those records. It would also look for records (over 30 and under 60 days) and do likewise etc. -- Todd Chittenden [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] [quoted text, click to view] "Andy" wrote:
> On Feb 12, 8:55 am, Joe Webb <j...@webbtechsolutions.com> wrote:
> > I'd probably configure the SQL Server Event Provider to query the database
> > once per day, looking for contract expirations that occur on the intervals
> > defined.
>
> Joe,
>
> It looks like I can schedule the provider to run once a month. Would
> that be a valid approach?
>
> If I go the 'once a day' route, would I only include those that expire
> EXACTLY 90 days from now?
>
> Thanks
> Andy
>
[quoted text, click to view] On Fri, 16 Feb 2007 06:49:59 -0800, Andy wrote:
> On Feb 12, 8:55 am, Joe Webb <j...@webbtechsolutions.com> wrote:
>> I'd probably configure the SQL Server Event Provider to query the database
>> once per day, looking for contract expirations that occur on the intervals
>> defined.
>
> Joe,
>
> It looks like I can schedule the provider to run once a month. Would
> that be a valid approach?
>
> If I go the 'once a day' route, would I only include those that expire
> EXACTLY 90 days from now?
>
> Thanks
> Andy
Andy - If you go the once per month interval route, I don't think you'd be able to notify subscribers on the 30, 60, and 90 day intervals as needed. I'd look into scheduling it to occur daily; the match rule would look for the intervals needed. You could use mod 30 and less than 4 or some such to limit it to only those intervals needed. HTH... Joe -- Joe Webb SQL Server MVP http://www.sqlns.com
[quoted text, click to view] On Feb 28, 4:36 pm, Todd C <T...@discussions.microsoft.com> wrote:
> I'm certainly no expert in NS, but had a thought that might help:
>
> Why not set up a chronicle table with thee extra fields (DateTime) in it for
> 90, 60, and 30 days. Have a Scheduled subscription that runs once per day.
>
> When the subscription runs, it looks for any records in the (over 60 but
> under 90) day range and updates the appropriate field in the chron table for
> those records. It would also look for records (over 30 and under 60 days) and
> do likewise etc.
Todd, I've done something similar, but I have a single field which contains 30, 60 or 90. The event provider runs once per day. The email that is sent out contains all the data, and says 'Contracts expiring in 90 days or less.'
[quoted text, click to view] On Mar 1, 8:50 am, Joe Webb <j...@webbtechsolutions.com> wrote:
> If you go the once per month interval route, I don't think you'd be able
> to notify subscribers on the 30, 60, and 90 day intervals as needed. I'd
> look into scheduling it to occur daily; the match rule would look for the
> intervals needed. You could use mod 30 and less than 4 or some such to
> limit it to only those intervals needed.
Joe, I came to that conclusion as well, and have set it up to run daily. The notification itself will specify 60 days or less, 90 days or less, etc.
Don't see what you're looking for? Try a search.
|
