SQL Server Database Mail (Intro)

Good afternoon everyone!

Today, I will be discussing Database Mail, which is one of the SQL Server Manageability Features. This is only going to be an introduction as the database administrator should be the person who handles the management of the database, however I will end up covering this is much more detail at a later date (as I will start to discuss the more administrative parts of database handling).

What is Database Mail? 

Database Mail allows the SQL Server instance to send email messages with attachments to specific users. This can be useful when you want weekly reports (which aren't formatted) to be sent to certain employees to give them an update on the information stored within a particular database table.

Prior to SQL Server 2005, SQLMail was the service provided, however this was replaced with Database Mail (with SQLMail included for backwards compatibility only). Database mail communicates using SMTP (Simple Mail Transfer Protocol) and doesn't require third party software such as Microsoft Outlook or Windows Live Mail, which makes it especially suitable for use on especially dedicated database servers.

Benefits? 

So how can this benefit me in my development? Well... the features of Database Mail allow you to integrate email messaging along with your applications. All you need to do initially is configure Database Mail so it's linked to the SMTP account. For more information on how to do this, please read the following link:

http://databasebestpractices.com/configure-database-mail-sql-server-2008-r2/

Sp_send_dbmail and Associated Arguments

Once this step has been completed, you will have access to sp_send_dbmail (the pre-defined stored procedure for sending email messages via Database Mail) and all of its associated arguments:

• @profile_name
• The name of the mail profile where the message is sent from. 
• @recipients
• The email addresses of the people who intend to receive the message in the "To" field. 
• @copy_recipients
• The email addresses of the people who intend to be in the "CC" field. 
• @blind_copy_recipients
• The email addresses of the people who intend to be in the "BCC" field. 
• @subject
• The text that forms the subject line of the email message. 
• @body
• The content of the message. 
• @body_format
• States whether the email will be sent in text or HTML format. 
• @importance
• Sets the importance level of the message to either "Low", "Normal" or "High". Normal is the default value.
• @sensitivity
• Sets the privacy level of the message to either "Normal", "Personal", "Private" or "Confidential". Normal is the default value.  
• @file_attachments
• The list of file names that you wish to attach to your email message, each one separated by a semi colon (;). 
• @query
• Defines a query for the system to execute. 
• @execute_query_database
• Executes the query stored in @query. However if no query is defined then this argument will be skipped. 
• @attach_query_result_as_file
• A bit value that determines whether the results of the query should be returned as an attachment (1) or within the body of the email (0). However if no query is defined then this argument will be skipped. 
• @query_attachment_filename
• The file name of the attached query result. However if no query is defined or if the @attach_query_result_as_file returns 0, then this argument will be skipped. 
• @query_result_header
• Determines whether the column heading names will be included in the returning result set. However if no query is defined then this argument will be skipped. 
• @query_result_width
• Sets the number of line characters when formatting a returning query result set. The default is 256 characters. However if no query is defined then this argument will be skipped. 
• @query_result_separator
• Specifies the query result column separator character. The default is set to a space. However if no query is defined then this argument will be skipped. 
• @exclude_query_output
• A bit value which shows if there was an error with the query. 0 means that the query error message is being displayed on screen in the messages tab. 1 means that the command completed successfully, even if the query in the stored procedure has failed. 
• @append_query_error
• A bit value which indicates whether a message has been sent or not when a query error occurs. 0 means that the email message wasn't sent, and 1 means that the email was sent however the error message that did occur was attached to the email. 
• @query_no_truncate
• A bit value which can be set to determine whether a large column in a result set should be truncated (shortened). The default is 0, where the columns truncate to 256 characters. However it is possible to modify truncation options to increase or decrease that length, or set the @query_no_truncate to 1 which turns of truncation completely. 
• @mailitem_id [OUTPUT]
• Outputs the mailitem_id of the message. 

Modifying Configuration Settings of Database Mail

The following stored procedures can be used to modify the configuration settings of Database Mail:

• sysmail_configure_sp
• Configures Database Mail parameters. 
• sysmail_help_configure_sp
• Displays the current settings for Database Mail.
• sysmail_help_queue_sp
• Displays information on status and mail queues. 
• sysmail_delete_mailitems_sp
• Permanently deletes Database Mail tables from the system. 
• sysmail_delete_log_sp
• Permanently deletes Database Mail logs from the system.
• sysmail_start_sp
• Starts Database Mail.
• sysmail_stop_sp
• Stops Database Mail. 

Conclusion

And that is my introduction to SQL Server Database Mail. In the future, I will be posting more about managerial features in SQL, so please stay tuned for those other the coming weeks.

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.