Questions:
I want to send out custom email notification to specific groups/users for monitoring purpose. However, it consists of multiple select queries in MSSQL to get record count(s) for each application. I want to display my results nicely in table format; only "No of Records" column will be updated based on query. How to do it? I would like to have similar results that look like this. Thanks!
Answers:
First thing first, I would assume that you already have current SQL Agent/Job with Database Mail configured. If not, please follow the tutorial here; Configure Database Mail SQL Server. Otherwise it won't send out any email notifications as it need to sender emails. :)
1. To get the above layout, create a simple HTML file. There's no need to be all fancy. We can come back and add the styling later. So, it will look like this.
2. Open MSSQL and open your select scripts. It might look something like this.
 |
| Notes: This is just an example. |
3. Now, let's introduce variables to store the values for each select script to be plug-in into our HTML template later. It will look like this.
4. Let's bring in the HTML template into our SQL script. Don't forget to declare the variable to store the HTML. Paste all the HTML that you wrote in Step#1 to the variables.
5. To display value from our variables, just update the column that you wanted. It will look like this.
6. Now, let's try sending the email out by adding below command/scripts. Changed the profile name, recipient and subject accordingly.
7. Tadaaaa, if you want to set as SQL Job, just paste this script in. Easy right? 😎
Advanced
Some of you might want to display the results from one select query in similar table format. For example, Application, Process Description, etc all comes from same result set. No worries, it's still possible. Refer to below screenshot. Hope it helps! 😁😘
Related Entries
SQL: How to send custom email with attachment in MSSQL?