Tuesday, September 6, 2016

Sending Email in HTML Table Format Using TSQL in SQL Server

As we know that SQL language is specified as an ANSI and ISO standard and performance, scalability, and optimisation are important for database-driven applications, especially on the Web.  In application development, we are required to build a SQL program to perform certain task(s) on periodic basis or daily basis. For example - send an email alert with Department Wise Employee List from SQL Server to business users. Let us discuss how we can send an email in HTML table format using TSQL.

It is important to us before sending emails from SQL Server, we should have the following things-

To know all the above steps and their configuration, you can visit Enabling and configuring Database Mail in SQL Server using T-SQL

To understand this functionality, we can use the following Employee table structure to generate the tabular html list as given below: 
---- Create data table Employee
CREATE TABLE Employee
(
EmpId INT,
EmpName VARCHAR(25),
Department VARCHAR(25)
)

---- Insert Data into Employee
INSERT INTO Employee (EmpId,EmpName,Department)
VALUES (1, 'Ryan Arjun','Finance'),
(2, 'Kimmy Wang','Admin'),
(3, 'Lucy Gray','Sales'),
(4, 'Billy Doug','Admin'),
(5, 'Gery Dean','IT')

----Pull the data from Employee
SELECT EmpId,EmpName,Department
FROM DBO.Employee
EmpId
EmpName
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT
As we know that SQL Server provides msdb.dbo.sp_send_dbmail stored procedure to send emails to the business users and this stored procedure requires some parameters such as-
---- call SP_SEND_DBMAIL to send alerts
EXEC msdb.dbo.sp_send_dbmail
----SQL Email Profiler
@profile_name = @inpEmailProfiler,
----Who will receive the emails
@recipients = @inpToEmail,
----Type of format plain/html
@body_format=@inpBodyFormat,
---- Tabular design of data in html format
@body = @body,
---- Subject Line of the email
@subject = @inpSubjectLine;
How to create HTML table header
So, before creating the html tabular format, we should declare these variables and assign values in our T-SQL code as given below:
----- Declare local variables
----- Table header variable
declare @HtmlHeader nvarchar(500),
----- Table Body variable
@body nvarchar(max),
----SQL Email Profiler
@inpEmailProfiler VARCHAR(50) ='DBANotifications',
----Who will receive the emails
@inpToEmail VARCHAR(50)='youremail@xyz.com',
----Type of format plain/html
@inpBodyFormat VARCHAR(5) ='HTML',
---- Subject Line of the email
@inpSubjectLine NVARCHAR(200)='Department Wise Employee List';

----- HTML based tabular header
set @HtmlHeader='<html><style>table, th, td {border: 1px solid black;}</style>
<body width=400pt>
<table cellpadding=0 cellspacing=0 width=400pt>
  <tr>
  <td colspan=3 width=400 bgcolor="#ff9966" align="center"><B>Department Wise Employee List</B></td>
 </tr>
 <tr align="center">
  <td>Emp Code</td>
  <td>Employee Name</td>
  <td>Department</td>
 </tr>'
 ---- print table header variable
 Print @HtmlHeader

---- output of the header variable
<html><style>table, th, td {border: 1px solid black;}</style>
<body width=400pt>
<table cellpadding=0 cellspacing=0 width=400pt>
  <tr>
  <td colspan=3 width=400 bgcolor="#ff9966" align="center"><B>Department Wise Employee List</B></td>
 </tr>
 <tr align="center">
  <td>Emp Code</td>
  <td>Employee Name</td>
  <td>Department</td>
 </tr>
---- copy and paste the above output in MS Excel and see how it look like in email body
Department Wise Employee List
Emp Code
Employee Name
Department
How to create HTML table body
We have created our table header and need to populate the table body elements which will be pulled from our data table. To pull the data from database, we are using the CAST function of SQL Server as given below:
----- Generate HTML Table body from the database table
 ---- and set value in the local @body variable
  SET @body = CAST( (
  SELECT TD =CONVERT(VARCHAR(4),EmpId)+'</TD><TD>' + EmpName+'</TD><TD>' +Department
  FROM DBO.Employee
  FOR XML PATH('TR'), TYPE) AS VARCHAR(MAX));

  ---- concate html body header and body variable here
  set @body= @HtmlHeader+@body+ '</table></body></html><br>'
  ---- Replace <> tags here
  set @body=Replace(Replace(@body,'&lt;','<'),'&gt;','>')

  ---- Print body variable
  Print @body

---- output of the @body variable
<html><style>table, th, td {border: 1px solid black;}</style>
<body width=400pt>
<table cellpadding=0 cellspacing=0 width=400pt>
  <tr>
  <td colspan=3 width=400 bgcolor="#ff9966" align="center"><B>Department Wise Employee List</B></td>
 </tr>
 <tr align="center">
  <td>Emp Code</td>
  <td>Employee Name</td>
  <td>Department</td>
 </tr><TR><TD>1</TD><TD>Ryan Arjun</TD><TD>Finance</TD></TR><TR><TD>2</TD><TD>Kimmy Wang</TD><TD>Admin</TD></TR><TR><TD>3</TD><TD>Lucy Gray</TD><TD>Sales</TD></TR><TR><TD>4</TD><TD>Billy Doug</TD><TD>Admin</TD></TR><TR><TD>5</TD><TD>Gery Dean</TD><TD>IT</TD></TR></table></body></html><br>

 ---- copy and paste the above output in MS Excel and see how it look like in email body
Department Wise Employee List
Emp Code
Employee Name
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT
Send Email Alerts from SQL Server
We have already created our tabular html body and set the necessary values in the requested parameters. We are going to use msdb.dbo.sp_send_dbmail stored procedure to send emails to the business users with requested parameters such as-
---- call SP_SEND_DBMAIL to send alerts
EXEC msdb.dbo.sp_send_dbmail
----SQL Email Profiler
@profile_name = @inpEmailProfiler,
----Who will receive the emails
@recipients = @inpToEmail,
----Type of format plain/html
@body_format=@inpBodyFormat,
---- Tabular design of data in html format
@body = @body,
---- Subject Line of the email
@subject = @inpSubjectLine;

After executing the above script, you will get the email notification in your email inbox and it would be look like as –

T-SQL Code to send email alerts in a Glance :
----- Declare local variables
----- Table header variable
declare @HtmlHeader nvarchar(500),
----- Table Body variable
@body nvarchar(max),
----SQL Email Profiler
@inpEmailProfiler VARCHAR(50) ='DBANotifications',
----Who will receive the emails
@inpToEmail VARCHAR(50)='youremail@xyz.com',
----Type of format plain/html
@inpBodyFormat VARCHAR(5) ='HTML',
---- Subject Line of the email
@inpSubjectLine NVARCHAR(200)='Department Wise Employee List';

----- HTML based tabular header
set @HtmlHeader='<html><style>table, th, td {border: 1px solid black;}</style>
<body width=400pt>
<table cellpadding=0 cellspacing=0 width=400pt>
  <tr>
  <td colspan=3 width=400 bgcolor="#ff9966" align="center"><B>Department Wise Employee List</B></td>
 </tr>
 <tr align="center">
  <td>Emp Code</td>
  <td>Employee Name</td>
  <td>Department</td>
 </tr>'
 ---- print table header
 Print @HtmlHeader

 ----- Generate HTML Table body from the database table
 ---- and set value in the local @body variable
  SET @body = CAST( (
  SELECT TD =CONVERT(VARCHAR(4),EmpId)+'</TD><TD>' +   EmpName+'</TD><TD>' +Department
  FROM DBO.Employee
  FOR XML PATH('TR'), TYPE) AS VARCHAR(MAX));

  ---- concate html body header and body variable here
  set @body= @HtmlHeader+@body+ '</table></body></html><br>'
  ---- Replace <> tags here
  set @body=Replace(Replace(@body,'&lt;','<'),'&gt;','>')

  ---- Print body variable
  Print @body

  ---- call SP_SEND_DBMAIL to send alerts
EXEC msdb.dbo.sp_send_dbmail
----SQL Email Profiler
@profile_name = @inpEmailProfiler,
----Who will receive the emails
@recipients = @inpToEmail,
----Type of format plain/html
@body_format=@inpBodyFormat,
---- Tabular design of data in html format
@body = @body,
---- Subject Line of the email
@subject = @inpSubjectLine;

Conclusion

To enable your database email through T-SQL codes, you should have the sysadmin rights. This code is very useful especially when you’re setting up Database Mail for multiple instances and multiple SMTP servers. You can create store procedure to send various emails from multiple SQL instances which makes your life easier to automate these email alerts.

27 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thanks for this Article. I tried the same and its working fine.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. If you just posted a new website, but you do not have a HTML email form uploaded and you need to quickly provide a way for your visitors to contact you, then you should simply post your email address on your Contact page. Web Design Company

    ReplyDelete
  6. Web templates are created keeping the above three requirements in mind. website templates html5

    ReplyDelete
  7. How to make the first colum empid right justified ??

    ReplyDelete
  8. Thanks for this post, really useful. There's one more tool to find contact information: improver.io. It helps me to find personal emails and phone numbers.find email chrome extension

    ReplyDelete
  9. When you know which tasks and goals need to be met, you won't need to intervene now and then, that's unless it's necessary. drive traffic to website

    ReplyDelete
  10. I exploit solely premium quality products -- you will observe these individuals on:
    Joshua T Osborne

    ReplyDelete
  11. Man's lives, such as uncontrolled huge amounts, definitely not while countries furthermore reefs, challenging to seismic disturbance upward perfect apply. the best vpn for UK

    ReplyDelete
  12. I am looking for and I love to post a comment that "The content of your post is awesome" Great work https://internetprivatsphare.at/orf-streamen-im-ausland/

    ReplyDelete
  13. I was impressed with the site that you created, so memotipasi many people to be more advanced, there also kunjugi me, as a comparison click here

    ReplyDelete
  14.  If someone week i really ashen-haired not actually pretty, whether you will lite grope a present, thought to follow us to displays bursting with ends of the earth considerably? Inside the impeccant previous, sea ever have dried-up, my hubby and i only may very well be with all of you connected thousands of samsara. www.onsist.com

    ReplyDelete
  15. I am unquestionably making the most of your site. You unquestionably have some extraordinary knowledge and incredible stories.  vpnveteran

    ReplyDelete
  16. I got too much interesting stuff on your blog. I guess I am not the only one having all the enjoyment here! Keep up the good work. ethereum transaction alerts

    ReplyDelete
  17. The IT professionals generally face the most challenging roles in the decision making jobs. In the networking field many Microsoft Operating System Software are implemented through the Microsoft Certified Systems Engineer certification (MCSE) on the desktop and server.
    MCSE Training London

    ReplyDelete
  18. We are tied directly into the sate’s renewal database which allows us to process your request almost instantly. buy essays pressbusiness

    ReplyDelete
  19. Great post, and great website. Thanks for the information! hotmail sign in

    ReplyDelete
  20. This is a slightly complicated consideration when looking at getting a business coach because the dedicated time engagements are usually very much dependent on expertise,https://mailchi.mp/cc08635ff53e/solopreneursguide and location.

    ReplyDelete
  21. Particular interviews furnish firsthand message on mart size, industry trends, ontogeny trends, capitalist landscape and outlook, etc. Mehr Informationen

    ReplyDelete
  22. While having a short credit history increases your chances of rejection, a long credit history isn't always a savior too.bookkeeping space

    ReplyDelete
  23. So luck to come across your excellent blog. Your blog brings me a great deal of fun.. Good luck with the site. internetprivatsphare

    ReplyDelete
  24. Nice Informative Blog having nice sharing.. buying art platform

    ReplyDelete
  25. Our credit repair services work to fix past credit mistakes and verify credit report accuracy. Talk to a credit repair expert today!  lemigliorivpn

    ReplyDelete
  26. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info. יחצ

    ReplyDelete
  27. I'm happy to see the considerable subtle element here!. womenes business advice

    ReplyDelete

Popular Posts

Get Sponsored by Big Brands