Database Mail: Mailing Lists Directly From Microsoft SQL Server

Many people know that starting from version 2005 in SQL Server there is a built-in ability to send emails, which database administrators often use to send urgent alerts, for example, when scheduled tasks fail. However, few know that sending letters to SQL Server is possible directly from SQL queries, functions, and stored procedures. And if you have already configured mail in SQL Server once, it will take you just a minute to send a letter, and you can organize a whole mailing in 15-20 minutes. This system is called Database Mail (DBMail), and today I would like to share the experience of its use.

Customization

Before working with DBMail, you must first turn on the system and register in it a mail account (SMTP) from which emails will be sent. Unfortunately, the IMAP system is not able to work, but in most cases it is not necessary.

Configuring DBMail through the SQL Server administrator interface is very well and is described in detail in the article “Configuring Database Mail in MS SQL Server 2005 and later” , so I will not repeat it. I think this option will be more convenient for those who set up the system for the first time.

I, in turn, will show now an alternative option: how to set up programmatically through a SQL script. This option is not so visual, but it is faster and more convenient for those who need to configure DBMail on many servers or transfer settings from server to server, in this case it will be enough to simply run this script on a new server. It is only important not to forget that to use the following scripts you need to log in to the server as a member of the sysadmin group, otherwise the security system will indicate a lack of rights.
')
Let's get right to the point. Here is a script that prepares the server to use DBMail:

--   Service broker -      -- ,  DBMail IF (SELECT is_broker_enabled FROM sys.databases WHERE [name] = 'msdb') = 0 ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK AFTER 10 SECONDS GO --    DBMail sp_configure 'Database Mail XPs', 1 GO RECONFIGURE GO 

Next, you need to check whether the DBMail service is running:

 EXECUTE msdb.dbo.sysmail_help_status_sp 

And if it is not running (its status is not “STARTED”), then start it with a query

 EXECUTE msdb.dbo.sysmail_start_sp 

Now you need to create an SMTP account to send emails, create a mailing list administrator profile and connect an SMTP account to this profile. Suppose that the administrator of the MySite.ru site needs to organize a mailing list for registered users of his site, and for this he uses his mailbox admin@mysite.ru on the smtp.mysite.ru server.

 --  SMTP-    EXECUTE msdb.dbo.sysmail_add_account_sp --   @account_name = 'admin@mysite.ru', --    @description = N'  admin@mysite.ru', --   @email_address = 'admin@mysite.ru', -- ,      ":" @display_name = N' MySite.ru', -- ,        --    ,   "no-reply" @replyto_address = 'no-reply@please.no-reply', --   IP- SMTP- @mailserver_name = 'smtp.mysite.ru', --  SMTP-,  25 @port = 25, --  .       --        @username = 'admin', --     @password = 'MyPassword', --  SSL  ,  SMTP-   SSL @enable_ssl = 1; --      EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'MySite Admin Mailer'; --  SMTP-    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'MySite Admin Mailer', @account_name = 'admin@mysite.ru', --   SMTP-   @sequence_number = 1; --        DatabaseMailUserRole  MSDB EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'MySite Admin Mailer', @principal_id = 0, @is_default = 1; 

For reliability, it is recommended to create a pair of SMTP accounts for two different mail services and connect them to the profile. This will allow you to send an important letter even if there is no connection with one of the SMTP servers. In this case, for the priority SMTP account, the parameter @sequence_number of the procedure sysmail_add_profileaccount_sp must be equal to 1 (see above), for the spare account the parameter must be equal to 2.

It is recommended to mass mailing from the private domain mailbox. When trying to send from domains to public domains (mailboxes on yandex.ru, mail.ru, gmail.com, etc.), SMTP servers may consider you a spammer and block the mailing list or even the entire mailbox.

Firewall

Direct sending letters to SQL Server will be engaged in a separate program. In different versions of SQL Server, it is called “DatabaseMail90.exe” or “DatabaseMail.exe” and, by default, is located in the “C: \ Program Files \ Microsoft SQL Server \ ... \ MSSQL \ Binn \” folder. It is important not to forget to allow outgoing traffic for it in the firewall (firewall).

Test letter

It's time to try, is everything in order. Any user from the sysadmin group, the owner of the database (db_owner) MSDB, or a user with the DatabaseMailUserRole role can send a test letter. To add the DatabaseMailUserRole role to a user, use the standard sp_addrolemember procedure:

 sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '<_>'; 

Now let's send a test email:

 EXEC msdb.dbo.sp_send_dbmail --       @profile_name = 'MySite Admin Mailer', --   @recipients = 'friend@mysite.ru', --   @body = N'  SQL Server Database Mail', --  @subject = N' ', --        SQL- @query = 'SELECT TOP 10 name FROM sys.objects'; 

Additional parameters of the sp_send_dbmail procedure can be found in its description in MSDN .

If something is wrong, you first need to look at the status of the letter:

 SELECT * FROM msdb.dbo.sysmail_allitems 

And then look at the log:

 SELECT * FROM msdb.dbo.sysmail_event_log 

The most typical problems are discussed in the MSDN article “Troubleshooting Database Mail” , as well as in the article “SQL Server tasks for DBAs: Troubleshooting Database Mail” .

Successfully sent letters can be viewed with the following SQL query:

 SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems 

Mailing List

Now suppose that the administrator of the MySite.ru site needs to select from the table of users of his site those who have not visited the site for more than a year and send them invitations. We specifically take a real life situation to demonstrate more DBMail and SQL capabilities, including cursors and loops. To complicate the task, add a few more conditions:

• Letters should not be sent to those who indicated during registration that they do not wish to receive letters. For even greater complexity, we will define those who refused using a separate SQL query in a loop.
• Letters must be in HTML format.
• Letters should be sent at intervals of 3 seconds so as not to overload our SMTP server.
• The mailing list should not begin immediately after the launch of the SQL script, but, say, at 3 am.

 --     mysite USE mysite GO --  :  , , --     ,   DECLARE @user_id int, @user_name nvarchar(255), @last_login_date smalldatetime, @email_address varchar(255); --  @body       HTML DECLARE @body nvarchar(MAX); --  @no_mail   ,    DECLARE @no_mail int; --    ,      --        FAST_FORWARD DECLARE users CURSOR LOCAL FAST_FORWARD FOR SELECT id, name, last_login_date, email_address FROM users WHERE user_role IN (3,4,5) AND account_state = 2 AND email_address IS NOT NULL AND DATEDIFF(day, last_login_date, GETDATE()) > 365 ORDER BY id --      ,   3   WAITFOR TIME '03:00:00' --   users OPEN users --       FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address --      WHILE @@FETCH_STATUS = 0 BEGIN -- ,       SET @no_mail = (SELECT id FROM users WHERE id = @user_id AND allow_mail = 0) --  ,      IF @no_mail IS NOT NULL BEGIN PRINT N' ' + @user_name + N'   .' FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address CONTINUE END PRINT N'   ' + @email_address + N' ...' --    SET @body = N' <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> </head> <body> <p><img style="float:right;" src="http://mysite.ru/images/logo.png"/></p> <p>, ' + @user_name + N'!</p> <p>     MySite.ru.       ,  ,            .</p> <p> !</p> <p> ,   MySite.ru</p> </body> </html>'; --   EXEC msdb.dbo.sp_send_dbmail @recipients = @email_address, @subject = N'     MySite.ru', @body = @body, --      'HTML',  'TEXT' @body_format = 'HTML', --        --@file_attachments ='C:\attachment.jpg', --        --@copy_recipients ='me@gmail.com', -- "Blind copy"  "carbon copy" -    , --         --@blind_copy_recipients ='me2@gmail.com', --        @profile_name = 'MySite Admin Mailer'; --    3   ,    SMTP- WAITFOR DELAY '00:00:03'; --         FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address END --     CLOSE users GO 

All is ready. You can go relax.