I have a MSSql table that I would like to create an HTML email with totals. The SQL table has 4 fields
GroupName,Dept,Desc and ExpAmt. The GroupName would be the high Level then break on Desc and dept within the Desc.
GroupName Dept Desc ExpAMt
XZB 100 Exp 100
XZB 300 Exp 400
XZB 200 Deb 200
TFT 100 Exp 50
sample output:
XZB
exp 100 100
300 400
total 500
XZB
deb 200 200
total 200
TFT 100 50
total 50
Grand Total 750
Can this be coded in PS?
Thanks.
May 27, 2021 at 8:33 pm
Is there a reason you NEED to do this in powershell? it feels like it is the wrong tool to me when SQL Server can handle this without the need for a different application jumping in front.
If Powershell is the route you want (or need) to go, it can be used to pull data from SQL AND can be used to send email, so the answer is yes it can be coded in Powershell.
I'd recommend doing some research into getting data from SQL Server into Powershell as step 1. Once you have that working how you want, then sending the email is pretty easy and there are a lot of examples out there.
Note it does depend on your powershell version which is another reason I'd do it in SQL Server instead. SQL Server can handle it AND it works with every version of SQL Server that I know of. Note - I've only tested sending email on 2008 R2 through 2016, but I know it works in 2017 and 2019 and azure and I highly suspect it works in 2005 and 2000 as well. Heck, it probably works in 7 and 6.5 too.
Getting the formatting 100% right in SQL may take some trial and error, but I don't see why that couldn't be done. Probably will result in either a CTE (or multiple) or nested select(s).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 28, 2021 at 3:39 pm
Before looking at which tool to use to send an email - you need to build the query to return the results that you need. If you provide sample data and expected results in the form of create/insert statements - we can create a query to return those results.
For this - you want to look at GROUPING/GROUPING_ID functions with GROUPING SETS/ROLLUP/CUBE.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2021 at 7:17 pm
Jeffrey Williams is spot-on with his post above. I'll add that you should post the example data in a "Readily Consumable Format". Please see the article at the first link in my signature line for one way to do such a thing. Doesn't matter how you get there, though. It's the end result that would be much appreciated to help us help you. Thanks...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2021 at 12:32 am
Shifting gears a bit, here' a search that will lead you to many different fairly easy methods of doing simple things in HTML from T-SQL.
https://duckduckgo.com/?q=create+html+table+in+t-sql&ia=web
You can easily get a whole lot more sophisticated with things like the application of background colors, etc, based on the value of each "cell" in the output.
For example... I use the following code to have each machine send me a "Morning Jobs Report". I install it on each machine so that it can operate autonomously and I add a job on each machine to call the proc every morning at 8:00AM. Mind you, I do NOT have hundreds of machines. We've consolidated most of our stuff to just a few big ones. It gives me something to look at when having coffee in the morning. 😀 It color codes failures in cells as Red and also color codes last run dates as Yellow if more than a week old and Red if more than a month old. If even changes the color of the last updated date if a job was updated in the last week.
Note that there is a TODO in the code... look for TODO. You also obviously need to have email setup and enabled on your SQL Server. If you' rather not do the later, you could (I suppose) send the results to something else to do the emailing for you. And, yeah... I could have used CSS in the HTML but I felt that it was an unnecessary bit of extra effort because it's a super simple report that I've not needed to change since the last rev more than 6 years ago.
Even if you don't use it for it's intended purpose, it's not a bad example of what you can actually do in T-SQL with very little overhead. And, since I didn't do anything crazy, it's also self-scaling width-wise for most display devices. If you expect to read it on a smart phone, be advised that I never intended to ever use it on a smart phone and so have not only not tested it on a smart phone, I made no attempts at trying to make it fit the display of a smart phone or any similar small device.
Also... it's not nearly as long as it looks on this forum... which seems to nearly double-space single spaced lines of code.
CREATE PROCEDURE [dbo].[MorningReportJobs]
/**********************************************************************************************************************
Purpose:
Create a "Morning Report" of the status of all jobs on the Server/Instance
Usage:
EXEC dbo.MorningReportJobs 'somename@somedomain.com'; --Can have many addresses if needed.
Revision History:
Rev 00 - 07 Nov 2012 - Jeff Moden - Initial creation and test.
Rev 01 - 11 Nov 2012 - Jeff Moden - Redaction to more easily avoid the "Null Aggregate" messages.
Rev 02 - 09 Mar 2013 - Jeff Moden - 1. Use 2 part naming convention
2. Changes to make @pEmailList take direct email addresses.
Rev 03 - 11 Apr 2013 - Jeff Moden - Add fix for "-" as a RunDate
Rev 04 - 28 Dec 2015 - Jeff Moden - 1. Added color corrections for old NextRunDates
2. Added correction for IsScheduled
3. Added color if job modified in previous 7 days
4. Added/colorized the Last Run Date column.
**********************************************************************************************************************/
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Declare the I/O for this procedure
@pEMailList VARCHAR(MAX)
AS
--===== Environmental presets
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
--===== Declare the local variables
DECLARE @Server SYSNAME,
@From VARCHAR(8000),
@To VARCHAR(MAX),
@Subject VARCHAR(8000),
@Body VARCHAR(MAX)
;
--=====================================================================================================================
-- Create the address parts of the email
--=====================================================================================================================
--===== Create the parts of the email.
SELECT @Server = @@SERVERNAME,
@From = @Server + '@domain.com', --TODO! CHANGE THE DOMAIN NAME TO WHAT YOUR DOMAIN IS FOR THIS SERVER!
@To = @pEMailList,
@Subject = 'Morning Report: ' + QUOTENAME(@Server) + ' Jobs'
;
--=====================================================================================================================
-- Create the HTML body of the email
--=====================================================================================================================
--===== Create the BODY of the email as HTML
WITH
cteJobInfo AS
(
SELECT job.job_id,
JobName = job.name,
IsEnabled = CASE WHEN job.[enabled] = 1 THEN 'Yes' ELSE 'No' END,
IsScheduled = CASE
WHEN ot.FreqType = 1 THEN 'One Time'
WHEN ot.IsScheduled > 0 THEN 'Yes'
ELSE 'No'
END,
LastRunDate = lr.LastRunDate, --Added by Rev 04
NextRunDate = CONVERT(CHAR(16),nxt.SSDateTime,120),
LastModified = REPLACE(CONVERT(CHAR(19),job.date_modified,111),'/','-')
FROM msdb.dbo.sysjobs job
LEFT OUTER JOIN msdb.dbo.sysjobschedules sch
ON job.job_id = sch.job_id
OUTER APPLY util.IntsToSSDateTime(sch.next_run_date,sch.next_run_time) nxt
OUTER APPLY (SELECT ss.freq_type, ss.enabled --Rev 04 added ss.enabled
FROM msdb.dbo.sysschedules ss
WHERE ss.schedule_id = sch.schedule_id) ot (FreqType,IsScheduled) --Rev 04 added IsScheduled
OUTER APPLY (SELECT CONVERT(CHAR(16),MAX(SSDateTime),120) --Sub-Query Added by Rev 04
FROM msdb.dbo.sysjobsteps stp
CROSS APPLY util.IntsToSSDateTime(stp.last_run_date,stp.last_run_time)
WHERE stp.job_id = job.job_id) lr (LastRunDate)
)
,
cteJobHistory AS
(
SELECT hist.job_id,
MinRunDate = CONVERT(CHAR(16),MIN(run.SSDateTime),120),
MaxRunDate = CONVERT(CHAR(16),MAX(run.SSDateTime),120),
MaxDuration = CONVERT(CHAR(8),MAX(dur.SSDateTime),114),
TotalRuns = CONVERT(VARCHAR(10),COUNT(*)),
Passed = CONVERT(VARCHAR(10),SUM(CASE WHEN hist.run_status = 1 THEN 1 ELSE 0 END)),
Failed = CONVERT(VARCHAR(10),SUM(CASE WHEN hist.run_status = 0 THEN 1 ELSE 0 END)),
Retried = CONVERT(VARCHAR(10),SUM(CASE WHEN hist.run_status = 2 THEN 1 ELSE 0 END)),
Cancelled = CONVERT(VARCHAR(10),SUM(CASE WHEN hist.run_status = 3 THEN 1 ELSE 0 END))
FROM msdb.dbo.sysjobhistory hist
OUTER APPLY util.IntsToSSDateTime(hist.run_date,hist.run_time) run
OUTER APPLY util.IntsToSSDateTime(19000101,hist.run_duration) dur --19000101 is the "zero date"
WHERE run.SSDateTime >= DATEADD(mi,-1445,GETDATE()) --Finds last 24 hours and 5 minutes
AND hist.step_id = 0
GROUP BY hist.job_id
)
,
cteAssembleReport AS
(
SELECT jobinfo.JobName,
jobinfo.IsEnabled,
jobinfo.IsScheduled,
jobinfo.LastRunDate, --Added by Rev 04
NextRunDate = ISNULL(jobinfo.NextRunDate,'-'),
MinRunDate = ISNULL(jobhist.MinRunDate,'-'),
MaxRunDate = ISNULL(jobhist.MaxRunDate,'-'),
MaxDuration = ISNULL(jobhist.MaxDuration,'-'),
TotalRuns = ISNULL(jobhist.TotalRuns,'-'),
Passed = ISNULL(jobhist.Passed,'-'),
Failed = ISNULL(jobhist.Failed,'-'),
Retried = ISNULL(jobhist.Retried,'-'),
Cancelled = ISNULL(jobhist.Cancelled,'-'),
jobinfo.LastModified
FROM cteJobInfo jobinfo
LEFT OUTER JOIN cteJobHistory jobhist
ON jobinfo.job_id = jobhist.job_id
)
--===== Create the starting tags, table definition, title caption for the table, and column headers.
SELECT @Body =
'
<html>
<Body>
<table border="1" cellpadding = "4" cellspacing="0" style="font-family:arial; color:black; font-size:10pt;">
<caption style=" border:1; background-color:White; font-weight:bold; font-size:14pt;">
Morning Report - 24 Hour Job Summary for ' + @@SERVERNAME + '<br>
~ ' + CONVERT(CHAR(17),GETDATE(),113) + ' ~
</caption>
<tr style="background-color:Blue; color:White; ">
<th>Job Name</th>
<th>Is Enabled</th>
<th>Is Scheduled</th>
<th>Last Run Date</th>
<th>Next Run Date</th>
<th>Min Run Date</th>
<th>Max Run Date</th>
<th>Max Duration</th>
<th>Total Runs</th>
<th>Passed</th>
<th>Failed</th>
<th>Retried</th>
<th>Cancelled</th>
<th>Last Modified</th>
</tr>
--'
+ REPLACE(REPLACE(CAST(
( --=== This formats each column as HTML. CASE statements provide changes in background color.
SELECT
---------------------------------------------------------------------------------------------------------------
= 'left', td = JobName,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN IsEnabled = 'Yes' THEN 'background-color:White;' ELSE 'background-color:Yellow;' END,
= 'center', td = IsEnabled,'',
---------------------------------------------------------------------------------------------------------------
= CASE
WHEN IsScheduled ='Yes' THEN 'background-color:White;'
WHEN IsScheduled ='One Time' THEN 'background-color:DarkOrange;'
ELSE 'background-color:Yellow;'
END,
= 'center', td = IsScheduled,'',
---------------------------------------------------------------------------------------------------------------
--Added by Rev 04
= CASE
WHEN LastRunDate < DATEADD(dd,-30,GETDATE()) THEN 'background-color:Red;'
WHEN LastRunDate < DATEADD(dd, -7,GETDATE()) THEN 'background-color:Yellow;'
ELSE 'background-color:White;'
END,
= 'center', td = LastRunDate,'',
---------------------------------------------------------------------------------------------------------------
= CASE
WHEN NextRunDate = '-' THEN 'background-color:White;' -- Rev 03
WHEN IsScheduled = 'One Time' AND NextRunDate < DATEADD(dd,-30,GETDATE())
THEN 'background-color:Red;'
WHEN IsScheduled = 'One Time' AND NextRunDate < DATEADD(dd,-7,GETDATE())
THEN 'background-color:Yellow;'
WHEN NextRunDate < DATEADD(dd,-30,GETDATE()) THEN 'background-color:Red;' --Added by Rev 04
WHEN NextRunDate < DATEADD(dd, -7,GETDATE()) THEN 'background-color:Yellow;' --Added by Rev 04
ELSE 'background-color:White;'
END,
= 'center', td = NextRunDate,'',
---------------------------------------------------------------------------------------------------------------
= 'center', td = MinRunDate,'',
---------------------------------------------------------------------------------------------------------------
= 'center', td = MaxRunDate,'',
---------------------------------------------------------------------------------------------------------------
= 'center', td = MaxDuration,'',
---------------------------------------------------------------------------------------------------------------
= CASE
WHEN IsScheduled = 'Yes' AND TotalRuns = '0'
THEN 'background-color:Yellow;'
ELSE 'background-color:White;'
END,
= 'right', td = TotalRuns,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN Passed = TotalRuns THEN 'background-color:White;' ELSE 'background-color:Red;' END,
= 'right', td = Passed,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN Failed IN ('0','-') THEN 'background-color:White;' ELSE 'background-color:Red;' END,
= 'right', td = Failed,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN Retried IN ('0','-') THEN 'background-color:White;' ELSE 'background-color:Red;' END,
= 'right', td = Retried,'',
---------------------------------------------------------------------------------------------------------------
= CASE WHEN Cancelled IN ('0','-')THEN 'background-color:White;' ELSE 'background-color:Red;' END,
= 'right', td = Cancelled,'',
---------------------------------------------------------------------------------------------------------------
--Style added by Rev 04
= CASE WHEN LastModified <= GETDATE()-7 THEN 'background-color:White;' ELSE 'background-color:LightGreen;' END,
= 'center', td = LastModified,''
FROM cteAssembleReport
ORDER BY JobName
FOR XML PATH('tr'), TYPE --This concatenates everything nicely in <tr></tr> tags for each row in the final table.
)
--===== This formats the HTML for human readability for troubleshooting purposes. (End of CAST and REPLACE's)
AS VARCHAR(MAX)), '</tr>','</tr>'+CHAR(13)+CHAR(10)), '<tr>',SPACE(12)+'<tr>')
+ --=== This finalizes the starting tags.
' </table>
</body>
</html>
'
;
--=====================================================================================================================
-- Send the email
--=====================================================================================================================
--===== Send the email.
EXEC MSDB.dbo.sp_send_dbmail
@from_address = @From,
@recipients = @To,
@subject = @Subject,
@body = @Body,
@body_format = 'HTML'
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2021 at 4:25 pm
Here is a template I use for simple table based 'reports' sent from SQL Server. This can be used directly in an agent job - or used in a stored procedure. The first thing that is needed is a query that returns the 'detail' rows to be displayed in the report, then you can define the headers to match.
Set Nocount On;
Declare @body nvarchar(max)
, @xmlResults varchar(max)
, @tableHeader varchar(max)
, @recipients varchar(max) = 'user1@domain.com'
, @cc_recipients varchar(max) = 'user2@domain.com;user3@domain.com';
--==== Create the table header
Set @tableHeader = cast((Select html.hdr1 As th, ''
, html.hdr2 As th, ''
, html.hdr3 As th, ''
, html.hdr4 As th, ''
, html.hdr5 As th, ''
, html.hdr6 As th, ''
From (
Values ('Header1', 'Header2', 'Header3', 'Header4', 'Header5', 'Header6')
) As html(hdr1, hdr2, hdr3, hdr4, hdr5, hdr6)
For xml Path('tr'), elements) As varchar(max));
--==== Get the results as an XML table
Set @xmlResults = cast(( Select t.Detail1 As td, ''
, t.Detail2 As td, ''
, t.Detail3 As td, ''
, t.Detail4 As td, ''
, t.Detail5 As td, ''
, t.Detail6 As td, ''
From dbo.MyTable
For xml Path('tr'), elements) As varchar(max));
--==== Send Notification if we have results
If @xmlResults Is Not Null
Begin
--==== Setup the CSS style of the message
Set @body = '<style type=''text/css''>';
Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
Set @body += 'td {font-size:9pt; text-align:center; border:1px DarkCyan solid; padding:1px 5px 1px 5px;}';
Set @body += 'th {font-size:10pt; text-align:center; padding:2px 5px 2px 5px; background-color:DarkCyan; color:White;}';
Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
Set @body += '</style>'
--==== Setup the body of the message
Set @body += '<html><body>Simple paragraph description - can build out as needed
';
--==== Setup the table with the header and details
Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';
--==== Close the body and html
Set @body += '</body></html>';
--==== Send the HTML formatted email message
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'PrivateOrPublicProfileName'
, @from_address = 'SomeAddress@somedomain.com'
, @reply_to = 'AValidReplyToAddress@somedomain.com'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @subject = 'Subject'
, @body_format = 'HTML'
, @body = @body;
End
Go
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Here is a simple template for sending an attachment in 'Excel' format. The trick here is to tell Excel that the delimiter in the CSV file is a comma so it will open correctly when you open the file directly from email. You can define your delimiter as anything - a pipe, caret, semi-colon, etc. and Excel will open the file as a 'CSV' file.
Set Nocount On;
Declare @query nvarchar(max) = ''
, @recipients varchar(max) = 'user1@domain.com'
, @cc_recipients varchar(max) = 'user2@domain.com';
Set @query = '
Set Nocount On;
Select t.Column1 As [Sep=,' + char(13) + char(10) + 'Column1]
, t.Column2
, t.Column3
, t.Column4
, t.Column5
, t.Column6
From dbo.SomeTable;'
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'PublicOrPrivateProfile'
, @query = @query
, @subject = 'Subject'
, @body = 'Body Message Here'
, @recipients = @recipients
, @copy_recipients = @cc_recipients
, @execute_query_database = 'UserDatabase'
, @attach_query_result_as_file = 1
, @query_result_width = 8000
, @query_attachment_filename = 'Filename.csv'
, @query_result_header = 1
, @query_result_separator = ','
, @query_result_no_padding = 1;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply