Introduction
Sometimes there are tasks that require too much time to finish during business hours and we need to leave the office with some still pending like the migration of data, backups or long running queries. In such cases, we would still want to receive an email in our smartphones to make sure that the job was completed successfully.
For example, I want to receive an email at 10 PM from SQL Server with the status of the backup process.
In this article, we will show how to send SQL Server query results in emails to our Smartphone. It can be an Android, iPhone or any other device that supports email services. That way, we can verify, monitor and administer our SQL Server on-premises remotely.
We will divide the article in the following sections:
- Configure Database Mail in SQL Server
- Testing the mail configuration
- Test the sp_send_dbmail stored procedure
- Send queries and check query output
- Insert the current data and time in the email
- Work with HTML format in emails
Requirements
- A local SQL Server Management Studio (SSMS)
- SQL Server 2008 or later. In this example, SQL Server 2016 was installed (any edition except the Express edition).
- A smartphone with internet access and an email service installed.
Get started
Configure Database Mail in SQL Server
We will first configure SQL Server to receive emails.
In SSMS, go to Management>Database Mail right click and select the Configure Database Mail option:
It will start a Wizard to configure the email. Press the Next button:
Select the option to Set up mail by performing the following tasks:
You will specify the profile name here. In this example, the profile name is dcalbimonte. Press the Add button to associate an account. You can associate a Profile to multiple SMTP accounts:
In this example, I will connect using a Hotmail profile. The Outgoing SMTP server for Hotmail is now smtp.office365.com and the port used is 587. You may need to specify your credentials:
In this example, we are using Hotmail to send emails using the profile associated with the Hotmail account.
Here you have a list of SMTP outgoing services and ports:
| Service | Server Name | Port |
| Outlook | smtp.office365.com | 587 (SSL) |
| Gmail | smtp.gmail.com | 587 (SSL) |
| Yahoo | smtp.mail.yahoo.com. | 587 (SSL) |
| AOL | smtp.aol.com | 587 (SSL) |
In the next Window, we can convert the profile created to public and to make it the default one:
The system parameters can be used to configure the file size, retry attempts, retry delay and then extensions that are now allowed. You can also configure the Logging Level:
If everything is OK, press Finish:
The wizard will verify if the account is created, that the profile is created, that the account is added to the profile and that the access is granted to send mails:
You can verify that the profile was created by using the following stored procedure:
|
1 2 3 |
EXECUTE msdb.dbo.sysmail_help_profile_sp; |
When you run the procedure, it will show the profile information:
Testing the mail configuration
We will test in SSMS if we can send emails. To do that go to Management>Database and right click and select the Send Test E-mail:
Specify the email to send the test:
If everything is OK, in your Smartphone, the email will be received successfully:
Test the sp_send_dbmail stored procedure
In SQL Server 2005, there was an extended stored procedure to send emails. It was the xp_sendmail. For security reasons, the old xp_sendmail was removed because it required a MAPI profile, whereas sp_send_dbmail just requires the SMTP information.
In this example, we will show how to send an email using the sp_send_dbmail stored procedure:
|
1 2 3 4 5 6 7 |
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'dcalbimonte', @recipients = 'acalbimonte @gmail.com', @body = 'This is a sqlshack test.', @subject = 'This is a test' ; |
In this example, we are sending an email to a Gmail account. If we check your email in your phone, you will be able to verify that the SSMS is working fine:
There are other parameters that may be useful:
- @file_attachments – is used to send attachments in the email.
- @copy_recipients – is used to send Carbon Copies (CC) in the emails.
- @blind_copy_recipients – is used to Blind Carbon Copies (BCC) in the emails.
Send queries and check query output
In this example, we will run the sp_who stored procedure to run the current users, sessions, and process and send the results in an attachment by email to my smartphone:
|
1 2 3 4 5 6 7 8 9 |
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'dcalbimonte', @recipients = 'acalbimonte @gmail.com', @body = 'List of users', @subject = 'Users on the database' , @query = 'sp_who', @attach_query_result_as_file = 1 |
The sp_who will display the results as an attachment in the email:
If you open the file attached, you will be able to see the users, processes and sessions:
As you can see, sending procedures and receiving emails are straightforward processes.
Insert the current data and time in the email
Sometimes we need the current date and time in the email. The following example will show how to display the current date and time in the email subject:
|
1 2 3 4 5 6 7 8 9 10 11 |
declare @mysubject varchar(100)='Work orders '+cast(FORMAT(getdate(),'MM/dd/yy hh:mm:ss') as varchar(100)) EXEC msdb.dbo.sp_send_dbmail @profile_name = 'dcalbimonte', @recipients = 'acalbimonte@gmail.com', @body = 'List of workorders', @subject = @mysubject , @query = 'sp_who', @attach_query_result_as_file = 1 ; |
The cast is used to convert the dates to strings and the format function is used to provide a specified format to the dates. Finally, in the @mysubject variable, we send the dates and times concatenated with the string.
If you check your email in your smartphone, you can verify that the subject now includes the date and time:
Working with HTML format in emails
We can enrich our emails using HTML format in the body.
The next example, shows how to create a variable named @mybody of time varchar(max) and we are sending a HTML header (H1) and we are using bold fonts for the word report (strong tag):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
declare @mysubject varchar(100)='Work orders '+cast(FORMAT(getdate(),'MM/dd/yy hh:mm:ss') as varchar(100)) declare @myquery varchar(100)='sp_who' declare @mybody varchar(max)= '<h1>Work order report</h1>'+ '<p>Please find the <strong>report </strong>attached to this email.</p>' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'dcalbimonte', @recipients = 'acalbimonte@gmail.com', @body = @mybody, @subject = @mysubject , @query = @myquery, @attach_query_result_as_file = 1, @body_format = 'HTML' ; |
Note that you need to specify that the @body_format is HTML. If you check your email, you will notice that the header has a bigger font and the word report is bold:
You can use a CSS (Cascading Style Sheet) to provide a customized format to your emails. CSS is a language to describe the HTML presentation. For more information, refer to this link:
Conclusion
In this article, we learned how to configure SQL Server to send emails. We specified an SMTP, port, and credentials to send emails. In this example, we used Outlook.com webmail, however, we can use any SMTP server like Gmail, Yahoo, etc.
Once that the Profile is configured, we tested that it can send emails and then we tested the sp_send_dbmail stored procedure. We send a message, we send SQL Server queries and we show how to get emails with the query results attached.
Finally, we learned how to have emails with HTML format. If you want an example to send emails using the SQL Server Agent, please refer to the following article:
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023