Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. It is very important to configure mail on any server that you manage. Error and performance alerting as well as SQL agent job outcomes depend upon this service for notifications.
I am going to copy a paragraph directly from Microsoft TechNet since I can not say it in any better in my own words.
Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail.exe). The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers. The external process inserts an item in the Status queue for the outcome of the send operation. Inserting the new entry in the status queue starts an internal stored procedure that updates the status of the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.
I will be talking about the following topics today.
- Enabling the mail service
- Creating a mail account
- Making a mail profile
- Associating profiles with accounts
- Assign the profile a principle
- Sending that first email message
- Updating mail objects
- Deleting mail objects
Each topic has a set of system stored procedures to add, update, delete and view mail system objects.
As a surface area precaution, database mail is turned off upon installation. The following code enables the database mail service.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- -- Turn on database mail -- -- Just shows standard options sp_configure GO -- Turn on advance options sp_configure 'show advanced options', 1; GO -- Reconfigure server RECONFIGURE; GO -- Turn on database xp's sp_configure 'Database Mail XPs', 1; GO -- Reconfigure server RECONFIGURE GO |
The database mail service is just like any other windows service that can be stopped, queried, and started. The snipet of code below shows how to perform those tasks.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- -- Stopping and starting mail service -- -- See if database mail (DM) is started EXEC msdb.dbo.sysmail_help_status_sp GO -- Stop the service EXEC msdb.dbo.sysmail_stop_sp GO -- See if database mail (DM) is stopped EXEC msdb.dbo.sysmail_help_status_sp GO -- Start the service EXEC msdb.dbo.sysmail_start_sp GO -- See if database mail (DM) is started EXEC msdb.dbo.sysmail_help_status_sp GO |
There are many options that can be confiured to change how database mail works.
AccountRetryAttempts – The number of times that the external mail process attempts to send the e-mail message using each account in the specified profile.
AccountRetryDelay – The amount of time, in seconds, for the external mail process to wait between attempts to send a message.
DatabaseMailExeMinimumLifeTime – The minimum amount of time, in seconds, that the external mail process remains active.
DefaultAttachmentEncoding – The default encoding for e-mail attachments.
MaxFileSize – The maximum size of an attachment, in bytes.
ProhibitedExtensions – A comma-separated list of extensions which cannot be sent as an attachment to an e-mail message.
LoggingLevel – Specify which messages are recorded in the Database Mail log.
In this example, I change the maximum file attachment size to 20K bytes and add the batch file extension as a prohibited file attachment.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- -- General configuration options for mail -- -- Set the max attachment size to 20000 bytes EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize', '20000'; -- DM options - max file size EXEC msdb.dbo.sysmail_help_configure_sp 'MaxFileSize' -- Do not allow bat files as attachments EXEC msdb.dbo.sysmail_configure_sp 'ProhibitedExtensions', 'exe,dll,vbs,js,bat'; -- DM options - prohibited extensions EXEC msdb.dbo.sysmail_help_configure_sp 'ProhibitedExtensions' |
If it is possible, setting up two mail accounts on different SMTP servers gives your mail profile fail over capability.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
-- -- Creating two mail accounts with different SMTP servers -- -- not supplying @username, @password - defaults to anonymous connection -- Create a Database Mail account 1 EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'Public Account 1', @description = 'Mail account for use by all database users.', @email_address = 'dbmail@craftydba.com', @replyto_address = 'donotreply@craftydba.com', @display_name = 'SQL SERVER (CRAFTY 1)', @mailserver_name = 'smtp.1and1.com' ; GO -- Create a Database Mail account 2 EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'Public Account 2', @description = 'Mail account for use by all database users.', @email_address = 'dbmail@craftydba.com', @replyto_address = 'donotreply@craftydba.com', @display_name = 'SQL SERVER (CRAFTY 2)', @mailserver_name = 'smtp.1and2.com' ; GO -- Show the new mail accounts EXEC msdb.dbo.sysmail_help_account_sp; GO |
I am going to associate one mail profile with the two above accounts.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- -- Creating a mail profile -- -- Create a Database Mail profile EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = 'The Public Profile', @description = 'Profile used for administrative mail.' ; GO -- Show the new mail profile EXEC msdb.dbo.sysmail_help_profile_sp; GO |
Right now, the two accounts and one profile are seperate entities. The add profile account stored procedure associates one with the other.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- -- Linking the mail profile to the accounts -- -- Add the account 1 to the profile EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'The Public Profile', @account_name = 'Public Account 1', @sequence_number = 1 ; GO -- Add the account 2 to the profile EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'The Public Profile', @account_name = 'Public Account 2', @sequence_number = 2 ; GO -- Show the link between profile and accounts EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'The Public Profile'; GO |
At this point, no one have been given rights to use this profile. The snipet below gives the public principle rights to the profile.
1 2 3 4 5 6 7 8 9 10 11 12 |
-- -- Given public access to profile -- -- Grant access to the profile to all users in the msdb database EXEC msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'The Public Profile', @principal_name = 'public', @is_default = 1 ; -- Show the new default profile EXEC msdb.dbo.sysmail_help_principalprofile_sp |
The three examples of using sp_send_dmail are modified code from Tech Net tailored to this current talk. The most simplest execution of the send dbmail stored procedure is to craft a simple text message as shown below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- -- Send message - example 1 -- -- Plain text message EXEC msdb.dbo.sp_send_dbmail @profile_name = 'The Public Profile', @recipients = 'john@craftydba.com', @body = 'The stored procedure finished successfully.', @subject = 'Automated Success Message' ; GO -- The mail queue EXEC msdb.dbo.sysmail_help_queue_sp GO |
A real nice feature of send dbmail is that the results of a query can be embedded as an attachment.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- -- Send message - example 2 -- -- Send with query results as an attachment EXEC msdb.dbo.sp_send_dbmail @profile_name = 'The Public Profile', @recipients = 'john@craftydba.com', @query = 'SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder WHERE DueDate > ''2006-04-30'' AND DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' , @subject = 'Work Order Count', @attach_query_result_as_file = 1 ; GO |
Last but not least, dbmail can send emails that are formatted as HTML. This allows the developer to use query output to generate nice HTML tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
-- -- Send message - example 3 -- -- Send with embedded html table containing query data DECLARE @VAR_HTML NVARCHAR(MAX) ; SET @VAR_HTML = N'<h1>Work Order Report<h1>' + N'<table border="1">' + N'<tbody><tr><th>Work Order ID</th><th>Product ID</th>' + N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' + N'<th>Expected Revenue</th></tr>' + CAST ( ( SELECT td = wo.WorkOrderID, '', td = p.ProductID, '', td = p.Name, '', td = wo.OrderQty, '', td = wo.DueDate, '', td = (p.ListPrice - p.StandardCost) * wo.OrderQty FROM AdventureWorks2008R2.Production.WorkOrder as wo JOIN AdventureWorks2008R2.Production.Product AS p ON wo.ProductID = p.ProductID WHERE DueDate > '2006-04-30' AND DATEDIFF(dd, '2006-04-30', DueDate) < 2 ORDER BY DueDate ASC, (p.ListPrice - p.StandardCost) * wo.OrderQty DESC FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</tbody></table>' EXEC msdb.dbo.sp_send_dbmail @recipients='john@craftydba.com', @subject = 'Work Order List', @body = @VAR_HTML, @body_format = 'HTML' ; |
There are many tables in msdb that are part of the database mail system. A list of them are below.
- sysmail_account
- sysmail_attachments
- sysmail_attachments_transfer
- sysmail_configuration
- sysmail_log
- sysmail_mailitems
- sysmail_principalprofile
- sysmail_profile
- sysmail_profileaccount
- sysmail_query_transfer
- sysmail_send_retries
- sysmail_server
- sysmail_servertype
The log table contains an entry of each email try. A system stored procedure can be used to clean up the table if heavy usuage is performed.
1 2 3 4 5 6 7 8 9 |
-- -- View the log table & delete entries by date -- SELECT * FROM msdb.dbo.sysmail_log ORDER BY log_date DESC GO EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = '2011-09-29 17:08'; GO |
A mail items table contains the actual message that is sent. A system stored procedure can be used to clean up the table if heavy usuage is performed.
1 2 3 4 5 6 7 8 |
-- -- View the mail items & delete by date -- SELECT * FROM msdb.dbo.sysmail_mailitems ORDER BY send_request_date DESC GO EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = '2011-09-29 17:08'; |
All of the tasks above have stored procedures that can be used to update accounts, profiles, assoications between profiles / accounts and assignments of principles to profiles.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
-- -- Updates -- -- Change reply address to nothing EXEC msdb.dbo.sysmail_update_account_sp @account_name = 'Public Account 1', @replyto_address = ''; -- Show the new mail accounts EXEC msdb.dbo.sysmail_help_account_sp; GO -- Change profile description EXEC msdb.dbo.sysmail_update_profile_sp @profile_name = 'The Public Profile', @description = 'Profile used for my application.' ; GO -- Show the new mail profile EXEC msdb.dbo.sysmail_help_profile_sp; GO -- Re-arrange the sequence EXEC msdb.dbo.sysmail_update_profileaccount_sp @profile_name = 'The Public Profile', @account_name = 'Public Account 1', @sequence_number = 3; EXEC msdb.dbo.sysmail_update_profileaccount_sp @profile_name = 'The Public Profile', @account_name = 'Public Account 2', @sequence_number = 1; EXEC msdb.dbo.sysmail_update_profileaccount_sp @profile_name = 'The Public Profile', @account_name = 'Public Account 1', @sequence_number = 2; -- Show the link between profile and accounts EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'The Public Profile'; GO -- Make this not the default EXECUTE msdb.dbo.sysmail_update_principalprofile_sp @profile_name = 'The Public Profile', @principal_name = 'public', @is_default = 0 ; -- Show the new default profile EXEC msdb.dbo.sysmail_help_principalprofile_sp |
Anything that is created can be deleted in a instance. The delete stored procedures do just that with the mail objects.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
-- -- Deletes -- -- Remove principle EXEC msdb.dbo.sysmail_delete_principalprofile_sp @principal_name = 'public', @profile_name = 'The Public Profile' ; -- Show no default profile EXEC msdb.dbo.sysmail_help_principalprofile_sp -- Remove the links between profile and account EXEC msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'The Public Profile', @account_name = 'Public Account 1'; EXEC msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'The Public Profile', @account_name = 'Public Account 2'; -- Show no links between profile and accounts EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_name = 'The Public Profile'; GO -- Remove the profile EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = 'The Public Profile'; -- Show no mail profile EXEC msdb.dbo.sysmail_help_profile_sp; GO -- Remove the accounts EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Public Account 1'; EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'Public Account 2'; -- Show the new mail accounts EXEC msdb.dbo.sysmail_help_account_sp; GO |
The key points to remember from this article is that accounts are associated with SMTP servers. Public or private Profiles are setup for application use. Profiles are associated to accounts and given rights of usage to principles. All objects can be added, updated and deleted from the system. Most important of all, the sp_send_dbmail can send simple text messages, messages with queary output attachments, and very complex HTML message bodies. Given all these options, alerts, reports and notifications of application and/or system events can be created.
I will continue next time by talking about setting up operators for system alerting.
I really liked your blog! It helped me alot… Awesome. Exactly what I was looking for. Thanks!
As a Newbie, I am always exploring online for articles that can benefit me. Thank you
I like this weblog so much, saved to bookmarks. “Respect for the fragility and importance of an individual life is still the mark of an educated man.” by Norman Cousins.
Great post! I have a question…. is there a setting in dbmail that would make the mail service stop after a connection is closed so that you have to run the exec sysmail_start_sp everytime you want to send an email? I have never seen this before and am not sure what is causing it. Thanks!!!
Thank You, explains step by step, just what I was looking for!
Just what been looking for, I’ve tended to use wizard in the past
for setup. Now will be easy to build consistency across all world areas & environments. Big Thanks
Excellent article i was looking for this infomation thanks to share, the scripts is saving me time,
Awesome article, big thank you. Cannot get my head around one point, what is “messages with queary output attachments” ?