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.

45 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
  28. People love his products and services and are sharing them with others. bad credit personal loans in arkansas

    ReplyDelete
  29. Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you. אימון עסקי בפתח תקווה

    ReplyDelete
  30. Hello! I just wish to give an enormous thumbs up for the nice info you've got right here on this post. I will probably be coming back to your weblog for more soon! One Stop Online Magazine

    ReplyDelete
  31. This can bring in more revenue and keep your business startup on a path of success.
    click here

    ReplyDelete
  32. Thanks for the blog loaded with so many information. Stopping by your blog helped me to get what I was looking for. business

    ReplyDelete
  33. Wonderful article. Fascinating to read. I love to read such an excellent article. Thanks! It has made my task more and extra easy. Keep rocking. Travel Agency Invoice Template

    ReplyDelete
  34. So luck to come across your excellent blog. Your blog brings me a great deal of fun.. Good luck with the site. js bank car loan - meezan bank car financing

    ReplyDelete
  35. Thanks for the great information , i have learned alot from this article . hotmail sign in

    ReplyDelete
  36. thank you for this such a good information about hotmail email login on www.hotmail.com

    ReplyDelete
  37. Moreover, subtleties of the clumps to be obtained, the proportion of each cluster, the date of procurement, Top Online CFD Brokers 2019 the technique for benefit sharing, and the last date of value buy, ought to be clear so as to maintain a strategic distance from harm to each gathering's privileges and interests.

    ReplyDelete
  38. You make so many great points here that I read your article a couple of times. Your views are in accordance with my own for the most part. This is great content for your readers. visita este sitio

    ReplyDelete
  39. I havent any word to comprehend this declare.....truly i am inspired from this broadcast....the individual that make this nation it changed into a satisfying human..thank you for shared this associated with us. Guest Post

    ReplyDelete
  40. Individual data must be sufficient, pertinent and not exorbitant in connection to the reasons for which they were gathered
    pengeluaran hk

    ReplyDelete
  41. Pasarqq tempat Bermain judi bandarq online tentunya menjadi di antara pilihan yang tidak sedikit dipilih oleh pemain website bandarq .Bisa anda lihat profile terkait pasarqq ternama melalui metode klik link https://findery.com/pasarqq , lalu https://www.thebaynet.com/profile/noverakasanti888 kemudian https://fontlibrary.org/en/member/pasarqq/ dan juga http://www.myfolio.com/pasarqq.
    Selain itu juga pasarqq ini memiliki homepage personal seperti https://pasarqq.cabanova.com/
    http://rebuildingtogethernb.org/selamat-datang-di-situs-poker-online-pasarqq/
    http://sonidoslibertarios.org/selamat-datang-di-situs-poker-online-pasarqq/
    http://jonnymugwump.com/selamat-datang-di-situs-poker-online-pasarqq/

    ReplyDelete
  42. This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. From the tons of comments on your articles. תפקידו של יחצ

    ReplyDelete
  43. Rivalry has developed quick in the online business however you can at present get a portion of the pie. make money online

    ReplyDelete
  44. Awesome article, it was exceptionally helpful! I simply began in this and I'm becoming more acquainted with it better! Cheers, keep doing awesome! learn more about Hotmail

    ReplyDelete

Popular Posts

Get Sponsored by Big Brands