Use sp_send_dbmail to send newsletter to 15k? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Use sp_send_dbmail to send newsletter to 15k?

Author

Topic

holster100
Starting Member

13 Posts

Posted - 2008-09-17 : 09:03:56

Hello,

I have set up sp_send_dbmail to send me a newsletter at a test. I would now like it to send out to the 15,000 registered recipients I have in one of my SQL 2005 tables.

Is there a way of using sp_send_dbmail to send to multiple recipients in the stored procedure?

Would sp_send_dbmail be the most efficient way of doing it?

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-17 : 09:10:04

well you could send it in batches of 20 or so... no idea how long would that take.

but maybe you should look into an online option for this? i'm sure there are providers for this out there.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

holster100
Starting Member

13 Posts

Posted - 2008-09-17 : 09:12:54

The problem is, to send that many, providers cost a fortune! How do most jobs sites manage it (when they send out a daily email with the latest vacancies in your area). They must send out thousands!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-17 : 09:27:18

well this site uses http://www.enewsletterpro.com/
as i said before try sending it in small batches... now i have no idea how much time will it take for the email server to porcess them... that's the biggest issue with mass mail sending.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

holster100
Starting Member

13 Posts

Posted - 2008-09-17 : 10:12:28

I've just tried a test of 1000 emails in the table (all to me - I look so popular!). It took the SP 2 seconds to execute and queue all the mail.

I'm using a cursor to loop:

------------------------------------------------

Declare @Email as nvarchar(255)

Declare PersonCursor CURSOR FAST_FORWARD FOR

Select email from test_newslettersubs

OPEN PersonCursor
FETCH NEXT FROM PersonCursor
INTO @Email

WHILE @@FETCH_STATUS = 0
BEGIN

-- send the email

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'myprofile',
@recipients = @Email,
@body = @mystrhtml,
@body_format = 'HTML',
@subject = @subject;

FETCH NEXT FROM PersonCursor
INTO @Email
END

CLOSE PersonCursor
DEALLOCATE PersonCursor

-----------------------------------

Could anyone give me any pointers on how to batch this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-17 : 10:26:30

yes but how long did the smtp server took to send all 2000 to you?

by batching i meant to just put 10 emails in your @Email variable. no idea if that will be any faster since the smtp server always sends mails on the same thread as far as i know.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources