Using SQL Mail Part 2

Yesterday we talked about setting up SQL Mail.  Today we are going to talk about how we can take advantage of SQL Mail.  To do so, we need to be familiar with the sp_send_dbmail stored procedure.  This stored procedure allows for sending emails to a given list of recipients and many other options:

The format for the stored procedure looks something like this:

exec msdb.dbo.sp_send_dbmail @profile_name = 'profile_name'
    ,@recipients = 'recipients'
    ,@copy_recipients = 'copy_recipient'
    ,@blind_copy_recipients = 'blind_copy_recipient'
    ,@from_address = 'from_address'
    ,@reply_to = 'reply_to'
    ,@subject = 'subject'
    ,@body = 'body'
    ,@body_format = 'body_format'
    ,@importance = 'importance'
    ,@sensitivity = 'sensitivity'
    ,@file_attachments = 'attachment'
    ,@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

As you can see, there are many different options available when using this stored procedure.  Here are some items of note on the variables:

• @profile_name is the Profile Name used in the previous post.  The email will not go out if the profile_name is not spelled correctly.
• @recipients is a comma separated list of recipients.
• @body_format is the format to use for the body of the email.  The options are "Text" and "HTML".  The HTML options allows you to use HTML formatting for the email (which includes line breaks, etc.)
• @file_attachment is a comma separated list of files to attach to the email.
• @query (query to execute), @execute_query_database (database to execute the query on), @attach_query_result_as_file (selected whether to put the results as an attachment or in the body of the email, and @query_attachment_filename (the name for the attached query results) can all work together to give you different options when sending your email.

Once you have decided on your different options, you can then format your email message and include the SQL stored procedure when scheduling jobs or directly into stored procedures you have written.  You can then attach any results you want into the email.

As an example, let's say that I set up a stored procedure that emails customers once they have made a purchase.  I have variables that I would use in the stored procedure to perform this.  Those variables could include: @customernumber (customers account number), @ordernumber (order number), @customeremail (customers email address).  Using these variables, I could then send out an email on order like so:

declare @query1 varchar(4000) = 'select * from CustomerOrder where custombernumber = ' + @customernumber

exec msdb.dbo.sp_send_dbmail @profile_name = 'Test Profile'
    ,@recipients = @customeremail
    ,@from_address = 'myemail@gmail.com'
    ,@reply_to = 'myemail@gmail.com'
    ,@subject = 'Customer Order'
    ,@body = 'Attached is the information for your order'
    ,@body_format = 'HTML'
    ,@query = @query1
    ,@execute_query_database = 'MyDB'
    ,@attach_query_result_as_file = 1

To read more on SQL Mail, visit the following: SQL Mail

Using SQL Mail Part 1

You may have reports for your organization that you would like to mail out each day, but you don't have the time to run them manually.  So, what are your options?  They are as follows:

• Create an SSIS (SQL Server Integration Services) package and within it create a Mail process.
• Configure SQL Mail and use sp_send_dbmail stored procedure to send your reports.

The first choice (SSIS) is the simplest option and allows for a very minimal knowledge of T-SQL using system stored procedures.  The second choice allows for more options, but is also more complicated to set up.  The article will mostly focus on using the second option.

First, you have to configure Database Mail.  To do this, go in SSMS (SQL Server Management Studio), expand Management and then right click Database Mail (as seen below).  Then, select Configure Database Mail.

A window will then open.  In this new window, select Management Database Mail account and profile and click Next.

Then, select Create a new account and click Next.

In this new window, you will want to enter your mail settings.  Get it an account name and description.  The description should be one that you can use easily to identify the account later.  Then, enter your outgoing mail settings, which include e-mail address, display name, reply e-mail and server name/port number.

It should be noted that you can have a different Reply e-mail address from the E-mail address.  The e-mail address is used to send the email, while the reply e-mail is used when the person receiving e-mail hits reply.  This is useful if you need to send an e-mail from your address, but want any replies sent to a different e-mail address.

You will also need to enter your SMTP authentication, if need be.  Refer to your IT department to get these settings.

Once all information has been entered, click Next.

SQL will then give you all the steps that will be taken when the wizard finishes.  Click Finish.

If there were any errors, they will be shown in this screen.  Otherwise, click Close to finish the wizard.

Next, you will need to set up an e-mail profile.  Go back to the Configuration wizard and this time select Set up Database Mail by performing the following tasks.  Then click Next.

In this new window, enter your profile name and description.  From here, you will select which accounts you want assigned to the profile.  Now, why would you select more than one profile?  Redundancy, in the case one profile fails, it will go to the next profile.  Click Add.

Select your account from the Account Name drop down list and click OK.  If you want to add more than one account, click on the Add button again.

Your accounts will then be added to the list.  Click Next.

If you want your profile to be available for all databases, make it a public profile.  Click Next.

You can then change system parameters for your e-mails.  For example, most mail servers can handle files between 5MB and 10MB in size.  You can set your database mail to allow file sizes greater or lower using the Maximum File Size option.  Once you have made necessary changes, click Next.

You will then see a list of changes that will be made.  Click Finish.

SQL will then make the appropriate changes.  Click Close when done.

Tomorrow we will cover how we can then take advantage of SQL Mail in our processes.