SQL Server Database Mail

I use msdb.dbo.sp_send_dbmail to send email notification in SQL Server. Recently I migrated our DB to a new server. The problem I encountered is my SQL SP does not working any more.

Finally, I find out this is the SQL server configuration issues. There are some configuration settings to be done on SQL server level.

First, I have to turn the mail feature on by using the command:

  sp_configure 'Database Mail XPs', 1
  reconfigure

Next, I have to configure the Database Mail. Here is the picture to access the configuration:



where you have to create a Database Mail account and profile. Basically, the configuration contains information about mail server, SMTP Authentication. The profile will be created for you after you add a new account.

One thing you have to do in the Database Mail configuration is that to set the default profile as you will use in sp_send_dbmail so that you would not need to specify a profile.

For my case, I use this sending mail feature to send file attachment in some cases. My notification report mail contains information from DB in html format. The content may be very big. If the content is very big, it will take time to open the mail. In case of very large size content, I'll send the report as an attachment file.

By default, the file size for Database Mail configuration is 1,000,000 bytes. It is very small in most cases. I have to change it to 10,000,000 or about 10MB. This can be done in Database Mail configuration.



After all these settings, my email notification runs with success.