| Author |
Topic |
|
chrissy
Starting Member
17 Posts |
 Posted - 2002-07-17 : 15:05:57
|
| I'm sending a newsletter to 20,000 people using a stored procedure. I'm going to call the mailer object with sp_OACreate and after that, loop(?) through and send an email individually to each user. I get the user list from a newsletter table in the db.My question is, is there any efficient way around using cursors to loop through everyone in the newsletter list and send them an email?I pondered a WHILE but can't think of any way to do it.perhaps the cursor smasher has a suggestion ;)btw, thanks much for the tip about aspEmail. Its queue feature is awesome. I sent 3000 test emails last night in about 3 minutes, 20 seconds. :D |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-17 : 15:39:04
|
Um...quote:
I'm sending a newsletter to 20,000 people using a stored procedure...My question is, is there any efficient way around using cursors to loop through everyone in the newsletter list and send them an email? I pondered a WHILE but can't think of any way to do it.btw, thanks much for the tip about aspEmail. Its queue feature is awesome. I sent 3000 test emails last night in about 3 minutes, 20 seconds. :D
I see an awful lot of dots connecting themselves as if by magic... |
 |
|
|
acollins74
Yak Posting Veteran
82 Posts |
Posted - 2002-07-17 : 15:39:41
|
| Chrissy if you search the forum with the keyword cursor you will find plenty of useful examples that will be sure to answer your question. Also try searching google for an example. I found this one for you in a few minutes.http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci772081,00.htmlHope it helps |
|
|
|
chrissy
Starting Member
17 Posts |
Posted - 2002-07-17 : 15:48:47
|
| rob: pardon me for being dense, i dont get what you are saying ;)acollins, thanks for the tip. I've done a few searches on cursors on both the forums, the articles, google and the SQL Mag master CD..but kept coming up with irrelevant information.What keywords did you use for your search?Thanks much,Chrissy |
|
|
|
chrissy
Starting Member
17 Posts |
Posted - 2002-07-17 : 15:56:07
|
| btw..i have found this solution in the past as i remembered the WHILE but i couldnt remember the rest. This time, it's stickin! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-17 : 15:59:34
|
| Why not just queue the 20,000 emails using the system you had for 3,000?It's not a good idea to do bulk emailing using SQL Mail, it was never intended to do that kind of job. Even if it works, there are better ways to send large amounts of email. |
|
|
|
chrissy
Starting Member
17 Posts |
Posted - 2002-07-17 : 16:11:15
|
| Rob: the activeX component has a method where you do objMail.SendToQueue so it has to be called from somewhere. Its generally called from ASP pages, but 1. I don't want someone to have to manually load the page2. I don't want someone to have to wait while it executesThe sql server is a dual processor with lots of ram and scsi drives. Its for a 15-person company and the task will be scheduled to run after hours.I know that my approach is unconventional, but after evaluating the entire situation, I think it is an appropriate solution. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-17 : 16:32:21
|
| I think maybe this is what you're looking for:http://www.sqlteam.com/item.asp?ItemID=5908It eliminates the need to use sp_OA calls, which is pretty important with the volume of emailing you're sending. |
|
|
|
chrissy
Starting Member
17 Posts |
Posted - 2002-07-17 : 20:50:10
|
| Thanks. That actually provided a solution to a problem I didn't know I had. In SQL 7, the character limit passed to SP_OAs is 255. On SQL 2000, it appears to be 4000. Too small for the company's needs.I thought about doing it using activex instead of sp_OA but thought decided to go with sp_oa. Correct me if I'm wrong, but I can only use the ActiveX script via DTS, right?Btw, the SP_OA doesn't appear to be that inefficient for sending email. The 3000 emails in 3mins 30 seconds was actually done using sp_OA and a mail server on the same network. It sent about 14 emails per second..and the way I did it was for testing, so i created and killed the object each time. Still good performance :D The machine it is on is an single processor Athlon. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-17 : 22:18:44
|
quote:
Correct me if I'm wrong, but I can only use the ActiveX script via DTS, right?
No, you can create an ActiveX script task as a job step without needing to create a DTS package.It's not that sp_OA methods WON'T work, or that they won't work well, but you are getting near the limit of what can be reasonably expected from it. 3000 emails in 3 minutes ain't bad, but there are systems that can send that many in under a minute. Right now you're sending 20,000 emails; in a year or less, who knows how many? Since you've been doing this kind of work for a number of years now, you know that any successful project tends to spawn similar projects that take advantage of what currently works.  And you're already seeing some limitations in what this method can provide, i.e. the character limitation.It would be pretty unpleasant if the whole thing came to a halt for several days while you had to research another method of sending large volumes of email every day. I'm only suggesting that you look hard at another method that is more specifically tailored for sending mass emails, in anticipation of growth. In terms of tools, if SQL Server was a screwdriver, and you had a handful of nails, you could get by with using the screwdriver to hammer them in...but if you had to build a house...well, you need to look at hammers and such. |
|
|
|
chrissy
Starting Member
17 Posts |
Posted - 2002-07-17 : 23:20:06
|
| Very good points. I downloaded the script and just took a look at it and saw the .vbs extension. Everytime I thought of VB/VBScript, I only thought of it as a desktop app or a web app. My colleagues and I never put .vbs together in our heads. We completely looked over windows scripting for scheduled tasks and even [dont laugh ;)] made a RunASP.exe app that executed web (asp) pages on a scheduled task. Even after all those .vbs viruses appeared in my mailbox, I still didn't put it all together.Thanks again for sharing your experience. |
|
|
|
|
alternative to cursors?