Sending thousands of e-mails from SQL Server
-
Hi,
I have a project where I need to develop a solution to send e-mail message to thousands of recipients (approx. 100k or so). The recipients' e-mail addresses are stored in a SQL Server 2000 database table. The solution also needs to able to keep of track of which recipients read the message (Note: For this requirement, I'm thinking of including in the message body an "I have read this message" hyperlink which refers to an ASP page with code to track this)
Currently, there is a solution in place for sending e-mails. The solution is an ASP page that opens a recordset with all the e-mail addresses and uses the CDONTS server object to send the e-mail message to each recipient in the recordset.
The solution suffers from problems such as:
1) Script Timeout
2) Restart Issues (if an error occurs, the next time the script runs it does not know which recipients it already sent the message),
3) Also, Exchange Server's message queue gets backed up when it reaches an e-mail it cannot send.
The server environment is:
Exchange 2000
SQL Server 2000
What are some recommendations or thoughts on possible solutions (i.e. load balancing, etc.) to these problems?
Any help is greatly appreciated ....
JP
-
You might try something along these lines...
The ASP page sends an email that Exchange recognizes in some way. Perhaps it drops it in a folder, etc. Exchange can fire an event using code to retrieve the list of email addresses and fire off messages one by one. If you're trying to have the ASP page generate the emails, you could increase the Script Timeout value, but I'm not thinking this is the best way to go unless you just don't have the ability to touch the Exchange server.
What you probably want to do is create some sort of tracking table for whenever you want to send emails are to be sent. When a process kicks off, it writes an entry into that table. As each email is sent in turn, a field is updated on that table. That way, should the process break, if you have to restart it, the process will read the table, see if there are any entries. If so, it'll pick up where it left off.
As far as getting the read receipt, that's a hard one. No matter what you do, some folks aren't going to click on the link. If you try and configure the email to go out with a delivery or read receipt, some won't come back. If you haven't already, communicate that while it's great in theory, in practice you aren't going to get very good results on this one unless the link is an acknowledgement for something mandatory.
K. Brian Kelley
@kbriankelley -
Though many people complain about the xp_send/read mail functions, I have implemented a successful and reliable method of sending mails and processing the response. The nice thing is that if the process fails at any point, you have the repsonses still sitting in an exchange mailbox and can just restart the processing.
If you can trust the users to reply, leaving the original subjet line or contents in place, then this may work for you.
If you go the SQL Main route, I found that always starting your code with
exec master.dbo.xp_startmail eliminates issues with the process hanging.
-
One thing to try is to create a stored procedure that calls CDONTs and sends the mail. This way, you can schedule it to run as a job async and forget about calling it from asp or at least have asp initiate the job and return back to the client quickly.
You will also be able to update or track each recipient since you're in the SQL db.
I'm not at the office right now, so send me an email at msahoo@healthevolutions.com and I will be happy to send you the code to call CDONTs from SQL Server stored procedures.
-
thank you everyone for your input ...
msahoo, could you please post in this thread your code sample on how to call CDONTS from a stored procedure. i know you would have use the SQL Server OLE Automation system stored procedures (i.e. sp_OA...) but i've never used those procs before.
thanks everyone for your help ...
JP
-
I wrote a mass mailing application of this sort a while back and the best way is to use a queing table - just have your ASP page write a single row into this table providing the details of the recipient, which message to send, any modifications to the standard message etc.
A VB app or something similar can then sit and every minute or so check the table and send out any emails for rows that are in the table. This way your ASP page provides instant response and with the VB app timed on 1 minute your customers will not notice that a queue is being used. This approach will handle however many emails you wish to throw at it, with it processing through the queue it will just keep churning away until it's done.
Dave.
-
I've also worked on a similar system recently. I implemented a mail queue in a sql server table then used a stored procedure with a cursor to iterate through the queue and send the mails. The queue was then updated depending on whether the mail was sent successfully or not. You can decide for yourself whether you want to keep a record of sending the mail as an audit trail.
You could call this SP from a scheduled task within SQL Agent to account for failures or the mail system, the overhead of checking for records still in the table would be minimal.
I would also recommend looking at xp_smtp_sendmail from http://www.sqldev.net. This will allow you to send HTML format messages nice and easily as I believe you're limited to plain text through xp_sendmail. The advantage of doing it through this xp is that it's all within SQL server, no need to go out to CDONTS yourself.
Tony
-
Our application sends about 15 - 20 k emails each day and it does it pretty smoothly ...Not many glitches
We have a Queue table which has a row inserted when an email needs to be fired. A sql stored proc run as a job scheduled to run every 2 minutes generates an xml and passes it on to a component which sends the emails and once the emails are sent there is a flag thats updated on a log table which keeps records of every email sent.
Ideally, what we have done is to eliminate the load on the SQL Server by placing the eMailing portion into components and sending xml with all the data rather than open connections.
Read Reciepts is not as easy as it seems. We havent been able to successfully handle that till now.
Cheers!
Arvind -
If you can compose and deliver and HTML email, many systems use a small 1x1 pixel white image to track reads. They create a few million unique ones, mail the link inside the message and when the image is retrieved, they mark a read. They can also reuse the image at that point.
-
Please clearify: Exchange Server's message queue gets backed up....
We send close to 50k emails every morning, using Windows2003, CDONTS, Exchange and SQLServer. The scripe is written in VBScript and runs in about 1.5 hours. Exchange BARELY/RARELY hiccups.
What is your "sendusing" set to ? If you move from a pure ASP page to a .Net app, you can add better error/recoverability handing and automate this process quite easily.
-
Here's my code for calling CDONTs from SQL Server. I wrote this a long time ago, so it may not be the best code. Feel free to optimize it.
First, I created a table for configuration settings that are set during the procedure execution. See the CDONTs documentation for setting properties. Since we sell a commercial application, I needed to have the configurations for CDONTs be "configurable" per installation. If you don't, you may want to hard-code the stored procedure to set these properties.
CREATE TABLE [dbo].[tblCdoConfigProperties] (
[CDOPropID] [int] IDENTITY (1, 1) NOT NULL ,
[Property] [varchar] (255) NOT NULL ,
[Setting] [varchar] (255) NOT NULL ,
[PropertyType] [varchar] (50) NOT NULL ,
[Active] [bit] NOT NULL
) ON [PRIMARY]
Values in the table (in respective order to the columns above separated by a space)
1 http://schemas.microsoft.com/cdo/configuration/smtpserver Need.SMTP.Here varchar 1
2 http://schemas.microsoft.com/cdo/configuration/sendusing 2 varchar 1
8 http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout 10 numeric 0
Below is the SP. Note, I use it to send HTML email content to the users. Feel free to modify to suit your needs, of course. You will need to build a SP that builds your email and then calls this SP.
CREATE procedure prCDOEmail
( @To varchar(255),
@From varchar(255),
@Subject varchar(255),
@HTMLBody varchar(8000)
)
AS
DECLARE @object int
DECLARE @hr int
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)
-- Create an object.
EXEC @hr = sp_OACreate 'CDO.Message', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-- Set the initial properties of CDO. The properties are stored in
-- a table so the installation can be custom configured.
DECLARE @Property varchar(255)
DECLARE @Setting varchar(255)
DECLARE @NumericSetting numeric
DECLARE @PropertyType Varchar(50)
DECLARE CDOCursor CURSOR FOR
SELECT 'Configuration.Fields("' + Property + '").value', Setting, PropertyType
FROM tblCDOConfigProperties
WHERE Active = 1
open CDOCursor
FETCH NEXT FROM CdoCursor INTO @Property, @Setting, @PropertyType
WHILE @@FETCH_STATUS=0
BEGIN
IF(@PropertyType = 'numeric')
BEGIN
SELECT @NumericSetting = CAST(@Setting as numeric)
EXEC @hr = sp_OASetProperty @object, @Property, @NumericSetting
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT 'Error setting property: ' + @Property, hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descCLOSE CDOCursor
DEALLOCATE CDOCursor
RETURN
END
ELSE
PRINT 'set ' + @property + ' to ' + @setting
END
ELSE
BEGIN
EXEC @hr = sp_OASetProperty @object, @Property, @Setting
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT 'Error setting property: ' + @Property, hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descCLOSE CDOCursor
DEALLOCATE CDOCursor
RETURN
END
ELSE
PRINT 'set ' + @property + ' to ' + @setting
END
FETCH NEXT FROM CdoCursor INTO @Property, @Setting, @PropertyType
END
CLOSE CDOCursor
DEALLOCATE CDOCursor
-- Get the OLE reference of the object to set the fields of the CDO email
EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-- Set To property.
EXEC @hr = sp_OASetProperty @object, 'To', @To
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-- Set From property.
EXEC @hr = sp_OASetProperty @object, 'From', @From
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-- Set Subject property.
EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-- Set HTMLBody property.
print 'Sending ' + cast(len(@htmlBody) as varchar(10)) + ' characters'
EXEC @hr = sp_OASetProperty @object, 'HTMLBody', @HTMLBody
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-- Send it off
EXEC @hr = sp_OAMethod @object, 'Send'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descRETURN
END
-
Hello everyone,
I have to prepare a application that captures subscriptions of users which they define in website and then send them the notifications whenever a match occurs as when required daily,weekly or monthly.
I have gone throuch many doc ,regarding this but i am confused which technology to use.I have gone through SQL Notification Services,SQl reporting Services , Sending mail through CDONTS.
Can somebody guide me which technolgoy to use.
Thanks in advance.Its urgent waiting for ur replies.
-
Hello everyone,
I have to prepare a application that captures subscriptions of users which they define in website and then send them the notifications whenever a match occurs as when required daily,weekly or monthly.
I have gone throuch many doc ,regarding this but i am confused which technology to use.I have gone through SQL Notification Services,SQl reporting Services , Sending mail through CDONTS.
Can somebody guide me which technolgoy to use.
Thanks in advance.Its urgent waiting for ur replies.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply