| Author |
Topic |
|
wgpubs
Yak Posting Veteran
67 Posts |
 Posted - 2004-10-01 : 18:12:34
|
| I use the CDOSYS object from SQL Server to send e-mails out. What I'm wondering is if there is a way to specifically tell SQL Server to send out e-mails based on a specific date/time associated with them? Currently I simply have a job ran once a day that looks for email objects (represented in a table) queued to send out ... what I'm looking for here is the ability to have the datetime associated with an email really drive when it is sent.Any thoughts?Thanks - wg |
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-10-01 : 18:36:54
|
| Do you need to have them sit in a table at all or do you want to simply process each email as it comes available?Derrick |
 |
|
|
wgpubs
Yak Posting Veteran
67 Posts |
Posted - 2004-10-01 : 18:51:20
|
| Ideally i want to process ea. email when their associated datetime to be sent hits. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-02 : 08:23:50
|
well can't you schedule a job for that?Go with the flow & have fun! Else fight the flow  |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2004-10-02 : 21:40:30
|
quote:
Originally posted by spirit1
well can't you schedule a job for that?
I was messing with this earlier.... You need to have your application add a schedule to a job using sp_addjobschedule. Or you can have a schedule that executes every minute that checks for emails that need to be sent and process each email that way. I ended up scrapping the sql job. It didnt seem like a good solution to create a cdonts object... #1 . it requires sysadmin permissions to call sp_OACreate or whatever its called.. So that may break portability.. #2 it costs alot to use that method.. Microsoft even says on one of there examples... "Usage of this method is up to the developer to maintain performance". But whatever... i assume you already have considered this... If your going to use a job... the best solution is to have the job that runs every minute or 2 and process all the emails in one go. Even though its not exactly on the nose... the effect is pretty much the same... Sometimes emails get put on queue anyways.. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-03 : 18:27:30
|
| how about if you write a service that runs on the sql server that checks the db on specified date and time, pulls records that need sending and sends them from the app???i was thinking about that problem for something i was doing and it seems like a feasible idea?any negative thoughts from anyone?Go with the flow & have fun! Else fight the flow |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2004-10-04 : 02:59:19
|
quote:
[i]any negative thoughts from anyone?
No spirit1. Only reason I jumped in here... I had just the same exact problem recently in my current project. I needed to schedule emails to send at an exact moment. I exhausted 2 days of research on doing this with sql jobs. Only to find out that I wasted my time because it didnt work well.Windows service is the recommended way. Only problem is you cant use windows service in a shared hosting environment. Luckily i use asp.net and was able to find a hack that allowed scheduled execution in the application object ( thanks to paul wilson ). I like your sig. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-04 : 03:44:13
|
what kind of hack?? any pointers, sites, words of wisdom... ?  my sig kind of describes me , so i like it too. thanx.Go with the flow & have fun! Else fight the flow |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2004-10-04 : 04:24:58
|
Yeah i have a link.. First let me sortof describe what it does in my own words. The asp.net application object is a global object. You can create static threads or timers in it that will execute at an application level. The problem with this is... you never know when an asp.net application object will terminate... and it wont start until a user hits the page. Paul wilson developed a base Global object that will poll your asp.net pages every 15 minutes ( you can change this ) and precompile them.. it also forces the application to stay alive. he doesnt garantee this.. but people have claimed to have it up for months without the application dying. Heres the linkhttp://authors.aspalliance.com/PaulWilson/Articles/?id=9I just use his base class to keep my application alive.. I use a thread that goes into an infinite loop that sleeps for 2 minutes on each loop. It polls the db and sends the emails public class Global : Forms.GlobalBase { private System.ComponentModel.IContainer components = null; private static Thread watcher; public Global() { InitializeComponent(); } private void mainThread() { // loop forever for(;;) { // process your emails System.Threading.Thread.CurrentThread.Sleep(1000 * 120) } } protected void Application_Start(Object sender, EventArgs e) { watcher = new Thread( new ThreadStart( mainThread )) watcher.Start() } |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-04 : 04:27:16
|
| sexy! usefull.Go with the flow & have fun! Else fight the flow |
|
|
|
|
Ability to send e-mails at a specific datetime