Sending query results as email in SQL Server

      2 Comments on Sending query results as email in SQL Server

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.

email1

Choose Set up Database Mail by performing the following tasks: and click Next. Enter a Profile Name and Description.

email2

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.

email3

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).

email4

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…

email5

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 = 'myemail@example.com',
  @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.

email6

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 + '' +
                '' + city + '' +
                '' + state + '' +
                '' + county + ''
      from uscities
      
set @tableHTML = N'

List of Cities - Aligned

' + N'
' + N'' + @table + N'
CityStateCounty
' exec msdb.dbo.sp_send_dbmail @profile_name = 'Example Profile', @recipients = 'myemail@example.com', @subject = @subjectStr, @body = @tableHTML, @body_format = 'HTML'

Using the series of statements above, you will get a cleaner result in your inbox.

email7

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.

2 thoughts on “Sending query results as email in SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.