I am pretty sure many of us used database mail stored procedures in order to send a simple email containing some data in a tabular format. You can find many articles where people use some tricks to form a table in HTML tags, for example employing XML. For example, you can see this one: http://www.sqlservercentral.com/scripts/sp_send_dbmail/66701/. That looks very cryptic and complex. Are there any simple solutions? Other people use SQL Server Reporting Services (SSRS) but it is not always available in some cases and developing SSRS reports requires more skill and often takes much longer than creating a custom solution using sp_send_dbmail.

Have you ever thought about creating a simple and reusable solution which can be used to deliver an email containing a tabular data? Something that much simple than SSRS reports that doesn't require any other components apart from the SQL Server database engine with database mail configured? I actually thought about that and created such a solution. I have seen some demand on this functionality, and now I want to share that solution with you. It is very simple to use and reusable. 

The Solution Description

In a nutshell, my solution consists of: 5 tables, 3 functions, and 2 stored procedures. The solution can be deployed in any database. It creates a schema, named 'report', and 10 objects in that schema. The solution can be managed by inserting records in the tables directly, but if you wish you can create a simple application which can be used to manage this solution. The database diagram for the solution is shown below:

Prerequisites and Installation

Prerequisites: you need to have a SQL Server database engine installed with the db mail feature enabled. A db mail profile has to be configured and work on that database engine, and a user database must exists on the server. You must have at least db_owner role in that database, and the schema, 'report', must not exist in the user database.

Installation: execute the attached script on the target user database that meets the prerequisites. The script and the solution were tested on SQL Server 2016 SP1, version 13.0.4457. This script and the solution should work on all versions of SQL Server from version 2005 to version 2017, but that is not guaranteed. The author has customized versions of this solution which work on SQL Server 2005/2008/2008R2/2012/2014/2016.

Configuration

The table, 'report.config' contains configuration parameters for the solution and doesn't have any relations with other tables in the solution. In this particular version, it can contain only one configuration parameter, which is 'DB mail profile name' with the id 1. Let's configure this parameter. On my database server I have a working db mail profile with the name 'mail', so I will use the next script in order to configure my solution:

insert into report.config(id, name, value)
values (1, 'DB mail profile name', 'mail');

We are done with the system configuration. Bear in mind that on your server, the db mail profile you want to use can have a different name.

I think that you already guess that the table 'report.recipient' contains records of emails to which a report is scheduled for delivery. Let's create one for demonstration. Please make sure that you have replaced a value 'my_email@mycompany.com' with a valid email you have access to.

insert into report.recipient (email)
values ('my_email@mycompany.com');

The next step is to create an actual report that would be delivered to your mailbox. The field subject is what will be in the subject line of the email. We add a record in the table 'report.report' like this

insert into report.report (subject)
values ('An example report');

I will need to deliver a content of some table, so I will create and populate a simple table, for example:

create table dbo.my_test_data
( id int, 
  code char(5),
  data nvarchar(100)
);
GO
insert into dbo.my_test_data(id, code, data)
values (1, 'CD001', N'My data line 1')
     , (2, 'CD002', N'My data line 2')
     , (5, 'CD005', N'My data line 5');

In order to deliver the content of this example table we need to create a record in the 'report.body' table. But firstly we need to query the 'report.report' table in order to find out the id of the report:

select id, subject
 from report.report;

In my case the report id is 1, you may see another number:

In order to add a table to the report we are creating a record in the 'report.body' table like this:

insert into 'report.body' (report_id, caption, table_name)
values (1, 'My test table', '[dbo].[my_test_data]');

And the last step we need to do is join a recipient of the report and the report itself. We know the report id from the previous step, in my case it is 1. Let's find out our recipient id:

select id, email from report.recipient;

In my case the recipient id is 1:

Then we add a join like this:

insert into report.report_recipient(report_id, recipient_id)
values(1, 1);

The relationship is many to many in this case, so each recipient can be subscribed to many reports, and each report can have several recipients.

Let's have fun and actually deliver a report using a 'report.send_report' procedure. The procedure accepts only one parameter – report id:

exec report.send_report 1;

If everything goes well you will see a message that email is queued:

If everything goes well you will see an email in your email inbox like the one in my email inbox:

If you haven't got the email or you have bumped into an error please check that your db mail profile works and that you have followed this instruction.

Advanced Configuration

The table 'report.body' has a nullable column, header,  which can be used to change column names and appearance of the table in the report. Let's check this feature on our example report. Change the header for the example table using the script below, in my case it has the id 1 but in your case, it may have another id. You should check the body.id in advance.

update [report].[body]
set header = '<table border="1"><tr><th bgcolor="#FFB0B0)">No</th><th bgcolor="#B0B0B0">Product code</th><th bgcolor="#B0FFB0">Product data</th>'
where id = 1;

Schedule the report again using the stored procedure 'report.send_report' as it was shown previously and check the result:

As you can see the appearance of our table has been changed accordingly with the HTML tags we used for that table.

You can add content of another table in the same email report, also you can use a view as a source of the data. Even more you can create a view that retrieves data from another database. For example, I have the example database 'WideWorldImporters' installed on my instance. Let's create a view in my user database that references data in the 'WideWorldImporters' database. I used the following SQL in order to create my view:

create view dbo.AZ_big
as
select 'USA' Country, 'Arizona' State, CityName City, LatestRecordedPopulation Population
from [WideWorldImporters].[Application].[Cities]
where StateProvinceID = 3 and LatestRecordedPopulation > 100000;

Let's add this view to my example report:

insert into [report].[body]([report_id], 
, [table_name])
values (1, 'The biggest cities in Arizona', '[dbo].[AZ_big]');

After executing of this report I got an email like this:

Isn't that cool?

Support, Distribution, and Customization

This solution distributes for free without any license or maintenance cost. Also there is no any support for this solution, you install it on your own risk and the author will not compensate any harm caused by this software or any harm caused by inappropriate usage of this software. You can install this solution on any number of database servers in your company.

You have rights to customize this software but you must not remove or change the author name from the header of the programmability modules.

In order if you wish to distribute this solution as part of your own commercial product or you want reproduce these ideas in your own product - you must contact the author directly.