Begrenzung der databaseMail-Sendequote

Wir verwenden DatabaseMail von SQLserver, um unsere Mails zu senden. Aber unser E-Mail-server-Anbieter erlaubt uns, 5 E-Mails pro Sekunde zu senden, die anderen werden einfach abgelehnt.

Ich muss herausfinden, dass Sie sich bei der database anmelden können.

Ein guter Ansatz ist, eine Warteschlange zu schaffen. Dies hat einen zusätzlichen Vorteil der Erstellung eines E-Mail-Logs.

DDL-Skript

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 

Gespeicherte Prozedur-Skripte

 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 

Anstatt die Ausführung von sp_send_dbmail auszuführen, benutze usp_queue_dbmail.

Erstellen Sie einen SQL Agent Job, der aus einem Schritt besteht. usp_send_dbmail_queue

Sie können den Job planen, um alle 1 Sekunde zu laufen.