Scenario

Every week I am supposed to send HTML newsletters to around 20,000 users who have registered on my client's web site. Earlier I used to send them all at once, which would sometimes have an impact on the relay server. One fine day a member of the Systems Team asked me whether is it possible to send the newsletters in a batch so that there is no impact on the Relay Server. I decided to implement the required logic. In the example below that I am going to explain you, I will be sending 3 newsletters at a time in a batch.

Pre-requisite

The following are required to implement the solution:

• SQL Server 2005(Except Express Edition) and above.
• A valid Database Mail Profile.

Steps involved

Create a sample table to hold the email addresses of the users. Here is the sample code which creates a table named tmpemailaddess to store the email addresses of the recipients to whom the newsletter is to be sent.

create table tmpemailaddress
(
id int identity(1,1),
cs_application_email varchar(100)
)

Next we insert some sample data.

Insert tmpemailaddress(cs_application_email)
select 'Email1'
union
select 'Email2'
union
select 'Email3'
union
select 'Email4'
union
select 'Email5'
union
select 'Email6'
union
select 'Email7'
union
select 'Email8'
union
select 'Email9'
union
select 'Email10'

I have used the Union operator because it removes the duplicate rows from the result set. After executing the insert statements, tmpemailaddress table will contain 10 email addresses.

Splitting the email address count

The email addresses of the users which is present in tmpemailaddress table is split in a series of 3 emails which is then stored in a temporary table named #store_id , later used by the logic written inside the cursor which fetches the user email addresses present in tmpemailaddress table lying between the start_id and end_id present in the #store_id table.

Create a temporary table named #store_id, which has three columns named reference_number,start_id and end_id.

Split the id of the email addresses, which is present in the tmpemailaddress table in ranges. Consider the below code

 set nocount on
 declare @start_id int
 declare @end_id int
 declare @final_id int
 declare @final_end_id int
 declare @count int
 set @count=2
 create table #store_id
 (
          reference_number int identity(1,1),
          start_id int,
          end_id int
 )
 select @start_id = id from tmpemailaddress order by id desc
 select @end_id = @start_id+@count from tmpemailaddress
 select @final_id = max(id) from tmpemailaddress
 while(@end_id<@final_id)
 begin
 insert #store_id
 select @start_id,@end_id
 select @start_id = @end_id+1 from #store_id
 select @end_id = @start_id+@count from tmpemailaddress
 select @final_end_id = end_id+1 from #store_id
 end
 insert #store_id
 select NULL,NULL
 update #store_id
 set start_id = @final_end_id
 where start_id IS NULL
 update #store_id
 set end_id = @final_id
 where end_id IS NULL
 select * from #store_id
 drop table #store_id

Explanation

After creating the temporary table named #store_id split the id's, which is present in tmpemailaddress table. Initially @start_id variable will contain the first id present in the tmpemailaddress table.

select @start_id = id from tmpemailaddress order by id desc

The @end_id variable will contain the value of @start_id+@count where @count will contain the number of newsletters to be sent in a batch.

 select @end_id = @start_id+@count from tmpemailaddress

After the while loop executes for the first time, the temporary table named #store_id will contain the following data:

reference_number start_id end_id

1 1 3

The @final_id variable will contain the maximum value of id present in tmpemailaddress table.

select @final_id = max(id) from tmpemailaddress

Subsequently @start_id will contain the value of @end_id+1 present in #store_id table and @end_id variable will contain the value of @start_id+@count present in tmpemailaddress table.

select @start_id = @end_id+1 from #store_id
select @end_id = @start_id+@count from tmpemailaddress

This process will continue inside the while loop until the value of the @end_id variable is less than @final_id. After executing the above set of T-SQL statements, the output is as follows:

In the above result, one thing we need to ensure is that as soon as the value of end_id column which is present in the 3^rd row reaches 9, which is one less than the max value present in the tmpemailaddress table, the next row shouldn't contain values ranging from 10 to 12. This is because the maximum value of the id present in tmpemailaddress table is 10. Though it will not have any impact on the results, still in order to ensure data consistency, I have included the below piece of code which will ensure that the next row will contain the start as well as the end value as 10.

insert #store_id
select NULL,NULL
update #store_id
set start_id = @final_end_id
where start_id IS NULL
update #store_id
set end_id = @final_id
where end_id IS NULL

First insert a row, which contain's NULL values for start_id and end_id, then populate the row using the update statements.

Using Cursors

The cursor will fetch the email addresses of the recipients present in tmpemailaddress table whose id fall between the start_id and end_id present in #store_id table for each row. After fetching the email address it will send the newsletter to that particular email address and log an entry in the sysmail_mailitemstable present in msdb database.

declare send_mail cursor for 
select cs_application_email from tmpemailaddress  
where id between @start_id_final and @end_id_final 
open send_mail                                          
fetch NEXT from send_mail                                                 
into @cs_application_email 
while @@FETCH_STATUS = 0                                                   
begin   
set @query = ' 
Will hold the source code of the HTML document, which you wish to send to the users. 
'
fetch NEXT from send_mail                                                 
into @cs_application_email 
end                         
waitfor delay '00:10:00'                   
close send_mail                                                  
deallocate send_mail    
set @varcnt = @varcnt + 1 
end 
set @query = ' 
Will hold the source code of the HTML document, which you want to send to the users. 
' 

@query variable will hold the source code of the HTML document, which we want to send to the users.

After one batch is executed the execution of next batch will be delayed using WAITFOR DELAY.

waitfor delay '00:10:00'                 

I have set 10 minutes as the time gap between successive batches.You can change it as per your requirement.

sp_send_dbmail

Sends an e-mail message to the specified recipients. When mail is successfully placed in the Database Mail queue, sp_send_dbmail returns the mailitem_id of the message. This can be found in the sysmail_mailitems table present in the msdb database. Also sp_send_dbmail stored procedure is present in the msdb database, so we use the three part name to execute the procedure.

EXEC msdb.dbo.sp_send_dbmail   
@profile_name = 'Profile Name',      
@recipients=@cs_application_email,    
@blind_copy_recipients ='Email Address', 
@subject ='Subject Of The EMail',     
@body = @query,     
@body_format = 'HTML' ; 

The parameters for this procedure can be found here.

Conclusion

We can conclude that sending the HTML Newsletters in a batch helps us to reduce the load on the relay server. Using this technique, you can split your data into groups that will allow you to better manage the load.

About Me

I am a Software Engineer from Mumbai University.I have 2.7 years of experience on Microsoft SQL Technology. I am currently working as a Database Consultant in Mumbai.