Are you struggling with sending a large number of HTML newsletters to your users without impacting your relay server? In this article, we will discuss a solution to this problem by implementing batch sending in SQL Server.

Prerequisites

To implement this solution, you will need:

• SQL Server 2005 (except Express Edition) or above
• A valid Database Mail Profile

Steps Involved

Let’s go through the steps to implement batch sending:

Step 1: Create a Sample Table

Create a table to hold the email addresses of the users. Here is an example code that creates a table named tmpemailaddress to store the email addresses:

CREATE TABLE tmpemailaddress
(
  id INT IDENTITY(1,1),
  cs_application_email VARCHAR(100)
)

Step 2: Insert Sample Data

Insert some sample data into the tmpemailaddress table:

INSERT INTO tmpemailaddress (cs_application_email)
VALUES
  ('Email1'),
  ('Email2'),
  ('Email3'),
  ('Email4'),
  ('Email5'),
  ('Email6'),
  ('Email7'),
  ('Email8'),
  ('Email9'),
  ('Email10')

Step 3: Split the Email Addresses

Split the email addresses into batches using a temporary table named #store_id. This table will store the start and end IDs for each batch. Here is an example 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 INTO #store_id (start_id, end_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 INTO #store_id (start_id, end_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;

Step 4: Using Cursors

Use a cursor to fetch the email addresses for each batch and send the newsletters. Here is an example code:

DECLARE @cs_application_email VARCHAR(100);
DECLARE @query NVARCHAR(MAX);

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.';
  
  -- Send the newsletter to the email address
  -- Log an entry in the sysmail_mailitems table
  
  FETCH NEXT FROM send_mail INTO @cs_application_email;
END

WAITFOR DELAY '00:10:00';

CLOSE send_mail;
DEALLOCATE send_mail;

SET @query = 'Will hold the source code of the HTML document, which you want to send to the users.';

Step 5: Sending the Newsletter

Use the sp_send_dbmail stored procedure to send the newsletter to the recipients. Here is an example code:

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';

Conclusion

By implementing batch sending of HTML newsletters in SQL Server, you can reduce the load on your relay server and better manage the sending process. This solution allows you to split your data into groups and send them in batches, ensuring a smoother and more efficient process.

About the Author

The author of this article is a Software Engineer with 2.7 years of experience on Microsoft SQL Technology. Currently working as a Database Consultant in Mumbai, the author has a strong background in SQL Server and is passionate about sharing knowledge and best practices.