SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 using sp_OACreate to send emails
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

attackmonkey
Starting Member

19 Posts

Posted - 01/28/2003 :  09:51:10  Show Profile  Reply with Quote
We're looking at using SQL server with sp_OACreate to call the CDOSYS component and send a bunch of emails (30,000+). Now I'm not sure if this is a good idea on a production server where there will be several large databases running as well. Has anyone had any experience of doing this on this scale? I'd be interested to know if my gut feeling (that this is a BAD idea) is correct, or if I am worrying needlessly.

Thanks in advance for any help and advice.

:)

Cheers,

Tim.


ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 01/28/2003 :  09:55:00  Show Profile  Reply with Quote
Depends on frequency of emails and size of emails and time required to create these emails.


Go to Top of Page

attackmonkey
Starting Member

19 Posts

Posted - 01/28/2003 :  10:01:47  Show Profile  Reply with Quote
You'd be looking at 100K+ multipart html/plain text emails going once a week or so, maybe more. Testing in the office, it maxed the sql server at 100% CPU for a protracted amount of time and slowed down all the sites that were using the database server.

Tim.

Go to Top of Page

ValterBorges
Flowing Fount of Yak Knowledge

USA
1429 Posts

Posted - 01/28/2003 :  10:04:29  Show Profile  Reply with Quote
What is the process for creating each email and how long does it take for 1 email?

Go to Top of Page

attackmonkey
Starting Member

19 Posts

Posted - 01/28/2003 :  10:14:32  Show Profile  Reply with Quote
Each email is a multipart text/html email that has a lot of customisation it (personalised greetings and stuff). The emails could potentially be 100K+ a piece, as they contain news articles for several days worth of content.

At the moment we are toying with having two tasks, one which generates all of the emails and saves then into a staging table, and then a second that could run as a DTS scehuled task to send the actual emails. Would this be viable?

Tests in the office sending 50,000 emails fromone of our dev boxes have shown the server maxing out CPU usage, and have substantially slowed down all the sites that use the database, even with minimal office traffic.

:(

Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 01/28/2003 :  12:39:36  Show Profile  Reply with Quote
have you've looked at...
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=9283
....???

which would help you schedule the intensive tasks for off-peak times...


there are several topics discussing this issue...search for "mail" in "articles"....there are several that may help you move along.

Go to Top of Page

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 01/28/2003 :  15:06:19  Show Profile  Send aiken an ICQ Message  Reply with Quote
I would recommend approaching this slightly differently -- create a table for outgoing email, and write a small external app to periodically check that table and send the email that needs to be sent. This will benefit you a few ways:

  • You can scale out the email sending app by going multi-threaded, running it on multiple machines, etc
  • Your SQL server won't be using sp_OACreate, sp_OADestroy, etc


We did the sp_OACreate thing on an NT4 box (CDONTS, not CDOSYS) and found that it led to stability problems -- after a few hundred thousand emails, the server would crash with the dreaded "unable to start thread process_login" (or something similar; it's been a while). Disabling the sp_OA stuff fixed the problem, re-enabling it caused the crashes again.

If you go with an external app for the actual sending of mail, I think you'll be much happier.

Cheers
-b

Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 01/28/2003 :  15:51:16  Show Profile  Visit MichaelP's Homepage  Reply with Quote
I've implimented a solution very similar to what aiken suggested, and it's been working great for years.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 01/28/2003 :  17:20:42  Show Profile  Visit Merkin's Homepage  Reply with Quote
<shameless plug>

http://www.sqlteam.com/item.asp?ItemID=5908

</shameless plug>

Damian
Go to Top of Page

attackmonkey
Starting Member

19 Posts

Posted - 02/03/2003 :  05:48:00  Show Profile  Reply with Quote
Thanks for all the help and advice guys, I've decided to try and persuade our lot to do something like what aiken has suggested. That way we can spread the sending load through several of our servers and stagger the sending tasks so that we have a couple of servers checking the mail quue at different points in time.

Thanks again guys!

:)

Tim.

Go to Top of Page

Gabriellerdx
Starting Member

Bulgaria
1 Posts

Posted - 06/13/2013 :  07:44:28  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000