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.