|
A step to remedying the issue of someone updating the table from outside your application is to add INSERT or UPDATE triggers on that table. The stored procedure can send a pipe message or email to wake up your monitoring thread to amend it's plan. I've used similar techniques for situations in which multiple events need to be accommodated; in your case it's:
1) New email added to the queue with a send time earlier than the earliest current one
2) Message from SQL Server to alert of a change in the queue table (via SMTP or named pipe)
3) Trigger time to send the first email(s) out
It sounds complicated, but the implementation isn't actually all that difficult. And it's a handy tool to have in your arsenal for future situations.
|
|
|
|
|
Well, do you really want your service to work with data in the database that has been tampered with?
Depending on the number of scheduled emails (due to memory constraints), you could have the service be the sole keeper of the email information - and only write to the database as a backup store. Then you have much more control over the process - the service gets and tracks the requests - if it is caching to the database (or other store), then it can control how long in the future to cache, and so will know when to pull from the db again. (Imminent tasks can also be stored in the db for failover purposes, but won't have to be looked up unless the service is restarted for some reason).
|
|
|
|
|
I implemented a scheduling feature in our application server, it works with a thread wich calculates the amount of time to sleep until the next action. If a new action is added, the thread is interrupted and it computes a new wake up delay.
|
|
|
|
|
What about getting SQL Server agent to do the scheduling? It's rather good at this. That way, you could create a job to run every minute say which just runs the query and appends the new emails to a queue table. Because all that's in the database domain you can make it transactionally safe etc, and should your windows service go down, you'll have everything queued up when it comes back up.
Then, you can poll your queue table quite frequently (say every second) because most of the time its going to be empty.
Just a thought.
Regards,
Rob Philpott.
|
|
|
|
|
Thanks. I see your logic but perhaps this is a flaw in my simplified requirements. The actual tasks that are going to run are not just emails being sent but the service will process other DB data and provide that information like an export function.
But I like your idea in terms creating a queue table, I will try to keep that in mind if the need arises
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
musefan wrote: other DB data and provide that information like an export function.
Actually you can do the entire operation within the (SQL Server/Oracle) database, including send the emails. This is how it used to be done before these new fangled service thingys turned up. SQL jobs have fallen out of favour in the last few years but the DB has a long history of doing this precise operation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have never done anything like that in SQL Server, but I imagine it would not cover all my needs. Thanks anyway thou
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
Trying to cycle every second is likely needless. I also wouldn't create a thread for each event.
I use a System.Timers.Timer for each of my Windows Services. For my Scheduler, I set it to poll every fifteen seconds (but it's configurable). When it cycles, it queries the database for any due tasks and executes them, be they reports or emails or Westminster Chimes or whatever. Sub-fifteen seconds ought to be timely enough for anyone, especially where email servers are concerned.
Also, each time the Service cycles it updates a timestamp in the database, so that I can tell that it's still cycling and not just hung up or dead.
One thought about sleeping for a future event is to sleep for only part of the time (90% for instance) and then sleep again until the duration is under some threshold.
|
|
|
|
|
I have done something similar to this using Quartz[^]. Quartz allows you to write your own 'job' class and to add it to its scheduling service with a specific firing time. I poll my DB table for new/changed jobs every few minutes, and submit the jobs to Quartz, which then takes on the precise timing without me having to worry about it. (Quartz is very good for scheduled - i.e. repeating - jobs and it doesn't sound like you want that, but worth a look nonetheless.)
Adam
|
|
|
|
|
I am using Quartz.NET for quite a while and I am very happy.
Especially creating own jobs based on existing interfaces made my life a little bit easier 
...I totally agree that it might be worth a look.
best regards
Andy
|
|
|
|
|
Databases and Regular Files (stream of bytes stored on disk) are both powerful mechanisms for data storage and retrieval. Remember that each has it's strengths: point is to take a moment to think if perhaps the database being polled for work versus a special file you devise (for speed's sake) that allows quick answers to the "Polling: Do what Now? question"
Second - There is a tool called SEC: Simple Event Correlator
I think you'll find this isn't as far removed from what you're trying to do as it might first look... this is the kind of system you are talking about - for general scheduling.. then you can toss in business logic to do all kinds of stuff.
The premise is this thing works by reading log files... and applying "rules" to them./.. basically.. if Log file A says "Server Down" ..say... you toss in rule.. 4 times in an hour - then this SEC thing would see the condition was met, then fire off some other task you said to do in this condition... say email IT server support, call the troops.. Perhaps if the same program see that 2 hours later server is still down - it sends out a email to let folks know "system X" is offline and is undergoing maintenance...
I'm all about writing fresh new cool useful systems when I can manage it.. but in this case.. I think you might have a few systems to pull from that you can "configure-code-customize" to do exactly what you need without to much heads-down new-development other than implementation work to automate what you need done... like sending those emails you were asking about
[edit]Conclusion: By using SEC or Splunk or other log file analyzing rule based engine - and perhaps simply writing a line of text to a log file to cause something to fire... or by just monitoring log files for "triggers" to respond to - action items... might work as a direct alternative to simple scheduled "cron" or "system tasks" being queued up.
[/edit]
Know way too many languages... master of none!
|
|
|
|
|
Thanks for the very detailed response - But I am sticking with a database table for many reasons.
I have already implemented a lot in this service I have created - but very little of it is actually used for the scheduling. Basically all it is is...
- Using one timer
- On start service, Reset timer based on difference between NOW and next scheduled intervral (every 15 minutes of the hour)
- On timer Tick, kick off a sync process to do what needs doing. Then reset the timer
- and on, and on, and.. hopefully on a bit more 
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
musefan wrote: next scheduled intervral (every 15 minutes
For that long a duration, I'd consider a Windows Scheduled Task.
|
|
|
|
|
It is configurable so it may change depending on how things go but after getting into the implementation (and how little is actually timer related) I can see your point for just having an application that just runs when scheduler tells it to.
I guess I shall just wait and see how things go, maybe there will be more to this service to come
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
musefan wrote: maybe there will be more to this service to come
You can burn that bridge after you've crossed it.
|
|
|
|
|
Dunno if this was suggested to you already, but in my opinion the only way to solve your problem without having to go through some kind of polling is by using triggers.
If your DB server allows you to execute compiled code in triggers you're the winner, since you can then signal your service upon insertion/deletion/update and adjust your schedule timer accordingly.
SQL Server can execute .NET code in triggers (see http://www.15seconds.com/issue/041006.htm[^] for example).
Good luck.
2+2=5 for very large amounts of 2
(always loved that one hehe!)
|
|
|
|
|
Have a look at NServiceBus or Rhino Service Bus. They do exactly what you want.
|
|
|
|
|
I think a dual approach should work.
Use the first job to look only for "unqueued" mails, and move them to a service, which is always on, that way you get the course grained (say next 15 minutes of) work and send the items to the service that can handle the next 900 seconds.... and you can then dynamically set the sleep in the service, to reduce cpu usage, or use the "down time" to see if any new items have been added/ get a leg up on the next cycle... this way if you really want you can ping or reschedual the Job from your service at will. (set it and.. Forget it..  )
I'd blame it on the Brain farts.. But let's be honest, it really is more like a Methane factory between my ears some days then it is anything else...
-----
"The conversations he was having with himself were becoming ominous."-.. On the radio...
|
|
|
|
|
I have had a lingering project with this type of feature. Unfortunately it is always pushed down the priority scale so I haven't implemented a solution.
However, I found are two very potential ways of handling it nicely. They seem to be a lot better than plain old Windows Scheduler or SQL jobs since you can write almost any .Net code you want for it. These are not in any particular order.
1) [Windows Workflow] - This has come a long way and might really work out for you.
2) [Quartz]- Open source enterprise job scheduler. I really want to try out this one. You can can even scale it across multiple servers if you wanted to. Seems pretty robust and has been around for a while. Built on top of .Net so it's definitely something you can probably dive into.
Let us know what you end up using. I'd really like to hear how it turned out.
|
|
|
|
|
from my c# code mail is send to few people but one people getting the same copy twice. i am very confuse about this situation why only one people getting the email of same copy twice when he downloads email from outlook. any idea please......!
thanks
tbhattacharjee
|
|
|
|
|
If you send by group, perhaps this people is in 2 group ?
If other people receive only once the mail, a one people twice. I think it is because this people appear twice in your sending.
|
|
|
|
|
Can you provide some code or tell us how the users register to get added to the distrubtion list? As it stands it coud be one of 100s of things!
|
|
|
|
|
Tridip Bhattacharjee wrote: from my c# code mail is send to few people but one people getting the same copy twice.
you should provide your Code in which problem is occur,
then we can easily manipulate what is problem.
otherwise it is very hard to say what is problem.
|
|
|
|
|
Just what Keith said almost three hours before you. Read the responses already given before adding to the discussion.
I know the language. I've read a book. - _Madmatt
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
