Why send emails in Microsoft SQL Server?
Sometimes you want to check a state of your table or calculate statistics on a table that updates regularly. Manually logging into a server and executing a query every single time is cumbersome and time consuming. Set up an automatic email in Microsoft SQL Server at regular schedule to facilitate your analysis process.
Configuring SMTP email
Open Microsoft SQL Server Management Studio and expand Management section. Right click on Database Mail and click Configure Database Mail.
Choose Set up Database Mail by performing the following tasks: and click Next. Enter a Profile Name and Description.
Click on Add.. to add an email server. Initially, you won’t have any servers to choose from, so click on New Account. Enter your email information and email server information. Be sure to choose the right port number and authentication method depending on your server. Most would require Basic authentication.
On the next page, configure whether you want to restrict access to the email per user or have it be public. Next page displays the default System parameters. You might have to play around with Maximum File Size parameter if the query result you are sending is larger than the default value (1 MB).
Proceed to Next and then Finish to create the profile. A green check mark means the profile was successfully added.
Send a test email
Back in the Object Explorer, right click on Database Mail again and click Send Test E-Mail…
Enter your email address in To: field and click Send Test E-Mail. If there was an error check the Database Mail Log by right clicking on Database Mail -> View Database Mail Log. If there was no error, check your inbox. You should have received the test email.
Send a query result as email
This is the basic syntax of sending an email:
exec msdb.dbo.sp_send_dbmail @profile_name = 'Example Profile', @recipients = 'firstname.lastname@example.org', @subject = 'Email title', @execute_query_database = 'DB Name', @query = 'Select * From table1'
After executing the above statement, you might receive a “Mail queued” warning. It simply means the email is ready to be sent and is in queue. You would have to execute a commit if you are in a manual commit mode. Make sure use your profile name and not your account name as @profile_name. Multiple @recipients must be delimited by semi-colons.
Above is a sample email received by a gmail account. Not very pretty. So you might want to include some HTML to at least make columns aligned properly.
declare @subjectStr varchar(500) declare @table nvarchar(max) declare @tableHTML nvarchar(max) set @table = N'' set @subjectStr = 'List of Cities - Aligned' Select top 50 @table = @table + '
' + '' from uscities set @tableHTML = N' ' + city + '' + ' ' + state + '' + ' ' + county + '
List of Cities - Aligned' + N'
Using the series of statements above, you will get a cleaner result in your inbox.
Now you can use the above code to create a stored procedure or use it to create a SQL Server Agent Job to run it on specific schedule. To read more about the sp_send_dbmail stored procedure, click here.