We are using DatabaseMail of SQLServer to send our mails. But our e-mail server provider is allowing us to send 5 e-mails per second, the other ones are simply rejected.
I need to figure out throttling DatabaseMail.
One good approach is to create a queue. This has an added benefit of creating an email log.
DDL Script
IF EXISTS ( SELECT *
FROM sys.objects o
JOIN sys.schemas s ON ( s.schema_id = o.schema_id )
WHERE s.name = 'dbo'
AND o.name = 'dbmail_queue'
AND o.type = 'U' )
DROP TABLE [dbo].[dbmail_queue]
GO
CREATE TABLE dbo.dbmail_queue
(
dbmail_queue_id BIGINT IDENTITY(1, 1) NOT NULL ,
profile_name SYSNAME NULL ,
recipients VARCHAR(MAX) NULL ,
copy_recipients VARCHAR(MAX) NULL ,
blind_copy_recipients VARCHAR(MAX) NULL ,
from_address VARCHAR(MAX) NULL ,
reply_to VARCHAR(MAX) NULL ,
[subject] NVARCHAR(255) NULL ,
body NVARCHAR(MAX) NULL ,
body_format VARCHAR(20) NULL ,
importance VARCHAR(6) NULL ,
sensitivity VARCHAR(12) NULL ,
file_attachments NVARCHAR(MAX) NULL ,
query NVARCHAR(MAX) NULL ,
execute_query_database SYSNAME NULL ,
attach_query_result_as_file BIT NULL ,
query_attachment_filename NVARCHAR(255) NULL ,
query_result_header BIT NULL ,
query_result_width INT NULL ,
query_result_separator CHAR(1) NULL ,
exclude_query_output BIT NULL ,
append_query_error BIT NULL ,
query_no_truncate BIT NULL ,
query_result_no_padding BIT NULL ,
mailitem_id INT NULL ,
mail_sent BIT NOT NULL ,
mail_queued_time DATETIME NOT NULL ,
mail_sent_time DATETIME NULL ,
CONSTRAINT pk_dbmail_queue PRIMARY KEY CLUSTERED ( dbmail_queue_id ASC )
);
GO
Stored Procedure Scripts
IF EXISTS ( SELECT *
FROM sys.objects o
JOIN sys.schemas s ON ( s.schema_id = o.schema_id )
WHERE s.name = 'dbo'
AND o.name = 'usp_queue_dbmail'
AND o.type = 'P' )
DROP PROCEDURE [dbo].[usp_queue_dbmail]
GO
CREATE PROCEDURE dbo.usp_queue_dbmail
@profile_name SYSNAME = NULL ,
@recipients VARCHAR(MAX) = NULL ,
@copy_recipients VARCHAR(MAX) = NULL ,
@blind_copy_recipients VARCHAR(MAX) = NULL ,
@from_address VARCHAR(MAX) = NULL ,
@reply_to VARCHAR(MAX) = NULL ,
@subject NVARCHAR(255) = NULL ,
@body NVARCHAR(MAX) = NULL ,
@body_format VARCHAR(20) = NULL ,
@importance VARCHAR(6) = NULL ,
@sensitivity VARCHAR(12) = NULL ,
@file_attachments NVARCHAR(MAX) = NULL ,
@query NVARCHAR(MAX) = NULL ,
@execute_query_database SYSNAME = NULL ,
@attach_query_result_as_file BIT = NULL ,
@query_attachment_filename NVARCHAR(255) = NULL ,
@query_result_header BIT = NULL ,
@query_result_width INT = NULL ,
@query_result_separator CHAR(1) = NULL ,
@exclude_query_output BIT = NULL ,
@append_query_error BIT = NULL ,
@query_no_truncate BIT = NULL ,
@query_result_no_padding BIT = NULL
AS
SET NOCOUNT ON
INSERT INTO [dbo].[dbmail_queue]
( [profile_name] ,
[recipients] ,
[copy_recipients] ,
[blind_copy_recipients] ,
[from_address] ,
[reply_to] ,
[subject] ,
[body] ,
[body_format] ,
[importance] ,
[sensitivity] ,
[file_attachments] ,
[query] ,
[execute_query_database] ,
[attach_query_result_as_file] ,
[query_attachment_filename] ,
[query_result_header] ,
[query_result_width] ,
[query_result_separator] ,
[exclude_query_output] ,
[append_query_error] ,
[query_no_truncate] ,
[query_result_no_padding] ,
[mail_sent] ,
[mail_queued_time]
)
VALUES ( @profile_name ,
@recipients ,
@copy_recipients ,
@blind_copy_recipients ,
@from_address ,
@reply_to ,
@subject,
@body ,
@body_format ,
@importance ,
@sensitivity ,
@file_attachments ,
@query ,
@execute_query_database ,
@attach_query_result_as_file ,
@query_attachment_filename ,
@query_result_header ,
@query_result_width ,
@query_result_separator ,
@exclude_query_output ,
@append_query_error ,
@query_no_truncate ,
@query_result_no_padding ,
0 ,
GETDATE()
)
GO
IF EXISTS ( SELECT *
FROM sys.objects o
JOIN sys.schemas s ON ( s.schema_id = o.schema_id )
WHERE s.name = 'dbo'
AND o.name = 'usp_send_dbmail_queue'
AND o.type = 'P' )
DROP PROCEDURE [dbo].[usp_send_dbmail_queue]
GO
CREATE PROCEDURE dbo.usp_send_dbmail_queue @QueueSize INT = '5', @Delay VARCHAR(12) = '00:00:01'
AS
DECLARE @dbmail_queue_id BIGINT ,
@profile_name SYSNAME ,
@recipients VARCHAR(MAX) ,
@copy_recipients VARCHAR(MAX) ,
@blind_copy_recipients VARCHAR(MAX) ,
@from_address VARCHAR(MAX) ,
@reply_to VARCHAR(MAX) ,
@subject NVARCHAR(255) ,
@body NVARCHAR(MAX) ,
@body_format VARCHAR(20) ,
@importance VARCHAR(6) ,
@sensitivity VARCHAR(12) ,
@file_attachments NVARCHAR(MAX) ,
@query NVARCHAR(MAX) ,
@execute_query_database SYSNAME ,
@attach_query_result_as_file BIT ,
@query_attachment_filename NVARCHAR(255) ,
@query_result_header BIT ,
@query_result_width INT ,
@query_result_separator CHAR(1) ,
@exclude_query_output BIT ,
@append_query_error BIT ,
@query_no_truncate BIT ,
@query_result_no_padding BIT ,
@mailitem_id INT
DECLARE queue_cursor CURSOR
FOR
SELECT TOP ( @QueueSize )
dbmail_queue_id
FROM dbo.dbmail_queue
WHERE mail_sent = 0
ORDER BY mail_queued_time DESC
OPEN queue_cursor
FETCH NEXT FROM queue_cursor INTO @dbmail_queue_id
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @profile_name = profile_name ,
@recipients = recipients ,
@copy_recipients = copy_recipients ,
@blind_copy_recipients = blind_copy_recipients ,
@from_address = from_address ,
@reply_to = reply_to ,
@subject = COALESCE([subject],'SQL Server Message') ,
@body = body ,
@body_format = body_format ,
@importance = COALESCE(importance, 'Normal') ,
@sensitivity = COALESCE(sensitivity, 'Normal') ,
@file_attachments = file_attachments ,
@query = query ,
@execute_query_database = execute_query_database ,
@attach_query_result_as_file = COALESCE(attach_query_result_as_file, 0) ,
@query_attachment_filename = query_attachment_filename ,
@query_result_header = COALESCE(query_result_header, 1) ,
@query_result_width = query_result_width ,
@query_result_separator = COALESCE(query_result_separator, ' ') ,
@exclude_query_output = COALESCE(exclude_query_output, 0) ,
@append_query_error = COALESCE(append_query_error, 0) ,
@query_no_truncate = query_no_truncate ,
@query_result_no_padding = query_result_no_padding
FROM dbo.dbmail_queue
WHERE dbmail_queue_id = @dbmail_queue_id
BEGIN TRY
SET NOCOUNT ON
IF @@VERSION LIKE 'Microsoft SQL Server 2005%'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
@recipients = @recipients,
@copy_recipients = @copy_recipients,
@blind_copy_recipients = @blind_copy_recipients,
--@from_address is not a Microsoft SQL Server 2005 parameter
--@reply_to is not a Microsoft SQL Server 2005 parameter
@subject = @subject, @body = @body,
@body_format = @body_format, @importance = @importance,
@sensitivity = @sensitivity,
@file_attachments = @file_attachments, @query = @query,
@execute_query_database = @execute_query_database,
@attach_query_result_as_file = @attach_query_result_as_file,
@query_attachment_filename = @query_attachment_filename,
@query_result_header = @query_result_header,
@query_result_width = @query_result_width,
@query_result_separator = @query_result_separator,
@exclude_query_output = @exclude_query_output,
@append_query_error = @append_query_error,
@query_no_truncate = @query_no_truncate,
@query_result_no_padding = @query_result_no_padding,
@mailitem_id = @mailitem_id OUTPUT;
ELSE
EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
@recipients = @recipients,
@copy_recipients = @copy_recipients,
@blind_copy_recipients = @blind_copy_recipients,
@from_address = @from_address, @reply_to = @reply_to,
@subject = @subject, @body = @body,
@body_format = @body_format, @importance = @importance,
@sensitivity = @sensitivity,
@file_attachments = @file_attachments, @query = @query,
@execute_query_database = @execute_query_database,
@attach_query_result_as_file = @attach_query_result_as_file,
@query_attachment_filename = @query_attachment_filename,
@query_result_header = @query_result_header,
@query_result_width = @query_result_width,
@query_result_separator = @query_result_separator,
@exclude_query_output = @exclude_query_output,
@append_query_error = @append_query_error,
@query_no_truncate = @query_no_truncate,
@query_result_no_padding = @query_result_no_padding,
@mailitem_id = @mailitem_id OUTPUT;
UPDATE dq
SET mailitem_id = @mailitem_id ,
mail_sent = 1 ,
mail_sent_time = GETDATE()
FROM dbo.dbmail_queue dq
WHERE dq.dbmail_queue_id = @dbmail_queue_id
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) ,
@ErrorSeverity INT ,
@ErrorState INT;
SELECT @ErrorMessage = 'Mail not sent. ' + ERROR_MESSAGE() ,
@ErrorSeverity = ERROR_SEVERITY() ,
@ErrorState = ERROR_STATE();
RAISERROR (
@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
END CATCH
FETCH NEXT FROM queue_cursor INTO @dbmail_queue_id
END
CLOSE queue_cursor
DEALLOCATE queue_cursor
WAITFOR DELAY @Delay
GO
Rather than executing sp_send_dbmail, use usp_queue_dbmail.
Create a SQL Agent job that consists of one step. usp_send_dbmail_queue
You can schedule the job to run every 1 second.