Solved

Email Query Results in HTML Using a SQL Server Agent Job

Posted on 2016-10-20
4
23 Views
Last Modified: 2016-10-21
We have got a basic query that we run on a daily basis that works really well. However we now want to format it into a HTML table.

Im new to the SQL statements and have got the below so far

SET @tableHTML =  
    N'<H1>Cash Trans</H1>' +  
    N'<table border="1">' +  
    N'<tr><th>Site Name</th><th>TransactionDateTime</th>' +  
    N'<th>TransactionID</th><th>Identifier</th><th>Amount</th></tr>' +  
    CAST ( ( SELECT td = cs.SiteName,       '',  
                    td = t.SiteTransDateTime, '',  
                    td = t.SiteTransID, '',  
                    td = pt.Type, '',  
                    td = -1 * tp.Amount, '',  
              FROM POS.dbo.ClientSite cs
            inner join POS.dbo.Transactions t on cs.SiteID = t.SiteID
            inner join POS.dbo.TransPayment tp on t.TransactionID = tp.TransactionID
            inner join POS.dbo.PaymentType pt on tp.PaymentTypeID = pt.PaymentTypeID
                           
              WHERE t.SiteTransDateTime > CAST(GETDATE() AS DATE)
            and pt.Type in ('Cash')
                            FOR XML PATH('tr'), TYPE  
    ) AS NVARCHAR(MAX) ) +  
    N'</table>' ;  
 
EXEC msdb.dbo.sp_send_dbmail @recipients='yourfriend@Adventure-Works.com',  
    @subject = 'Cash Trans',  
    @body = @tableHTML,  
    @body_format = 'HTML' ;  

The SQL statement is below
select
            cs.SiteName
            ,t.SiteTransDateTime as TranactionDateTime
            ,t.SiteTransID as TransactionID
            ,pt.Type as Identifier
            ,-1 * tp.Amount as Amount
      from
            ClientSite cs
            inner join POS.dbo.Transactions t on cs.SiteID = t.SiteID
            inner join POS.dbo.TransPayment tp on t.TransactionID = tp.TransactionID
            inner join POS.dbo.PaymentType pt on tp.PaymentTypeID = pt.PaymentTypeID
      where
            t.SiteTransDateTime > CAST(GETDATE() AS DATE)
            and pt.Type in ('Cash')

      Order By TranactionDateTime

Some pointers in the right direction as to where I am going wrong would be helpful
0
Comment
Question by:hmuser
  • 3
4 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41852534
Why don't make a stored procedure that will create a HTML table and send it in emal for any result set that you will come up with and not just for a particular case? The trick is to create a permanent table with the SELECT ... INTO tbl FRM... and then just use that table to build around it and send it in email.

You first create this stored procedure:
CREATE procedure [dbo].[spHtmlFromTableEmail]
	@database varchar(100)='YourDb', 
	@schema varchar(5)='dbo',
	@table_name varchar(100)='table_name',
	@columns_list varchar(8000)='ALL',
	@subject varchar(100)='',
	@body varchar(max)='',
	@email_list varchar(1000)='your.name@company.com',
	@cc_list varchar(1000)=NULL,
	@mail_profile varchar(100)='Database Mail',
	@send char(1)='N'
as

declare
	@headers nvarchar(max)='',
	@content nvarchar(max)='',
	@sql nvarchar(max)=''

if object_id(@database+'.'+@schema+'.'+@table_name) is null
begin
	DECLARE @msg NVARCHAR(MAX);
	SET @msg = N'Table doesn''t exist: ' + @database+'.'+@schema+'.'+@table_name

	RAISERROR(@msg,16,1);
	return
end

-- if the columns list is empty or ALL then build the list from INFORMATION_SCHEMA
if isnull(@columns_list,'ALL')='ALL'
begin 
	SET @columns_list=''
	select @sql=
'select 
	@content_out=@content_out+COLUMN_NAME+'',''
from 
	'+@database+'.INFORMATION_SCHEMA.COLUMNS 
where 
	TABLE_NAME='''+@table_name+''''
	exec sp_executesql @sql, N'@content_out nvarchar(max)='''' OUTPUT', @content_out=@columns_list output
	select 
		@sql='',
		@columns_list=left(@columns_list,len(@columns_list)-1)

end
	
	/*-- ### debug purpsoe only
	print @sql
	print @columns_list
	return	
	--*/

-- buld the HTML header and the sql for the table content	
select 
	@headers=@headers+char(9)+'<th> '+ cols.Elem+ ' </th>'+char(13)+char(10),
	@sql=@sql+char(13)+char(10)+char(9)+'ISNULL(cast(['+cols.Elem+'] as varchar(max)),'''') as '''+case when cols.Elem='row_css_class' and cols.ElemID=1 then cols.Elem else 'td' end +''','''','
FROM 
	[zb_dba_maint].[dbo].fnParseStringToSet(@columns_list,',') cols

select
	 @headers='<tr>'+char(13)+char(10)+@headers+'</tr>'+char(13)+char(10),
	 @sql='SELECT '+left(@sql,len(@sql)-1)+char(13)+char(10)+
	 'FROM '+char(13)+char(10)+char(9)+@database+'.'+@schema+'.'+@table_name+char(13)+char(10)+
	 'FOR XML PATH(''tr''), ELEMENTS'
select 
	@sql='SET @content_out = 
(
SELECT 
	replace(
		replace(
			replace(
				replace(
					replace(
						replace(
							replace(tbl,''<tr><row_css_class>Monday</row_css_class>'',''<tr class="monday">'')
						,''<tr><row_css_class>Tuesday</row_css_class>'',''<tr class="tuesday">'')
					,''<tr><row_css_class>Wednesday</row_css_class>'',''<tr class="wednesday">'')
				,''<tr><row_css_class>Thursday</row_css_class>'',''<tr class="thursday">'')
			,''<tr><row_css_class>Friday</row_css_class>'',''<tr class="friday">'')
		,''<tr><row_css_class>Saturday</row_css_class>'',''<tr class="saturday">'')
	,''<tr><row_css_class>Sunday</row_css_class>'',''<tr class="sunday"'') as htm
from
(
SELECT CAST(('+@sql+') AS VARCHAR(MAX))
) q (tbl))
'
--select @sql 
--return
exec sp_executesql @sql, N'@content_out nvarchar(max) OUTPUT', @content_out=@content output

select 
	@body='
<html>
<head>
<STYLE TYPE="text/css">
<!--
BODY
   {
   font-family:tahoma;
   font-size:11;
   }
table, td, th {
   margin: 1;
   padding: 1;
   border: 1px solid gray;   
   }
table {
   border-collapse: collapse;
   border-spacing: 1;
   }
TD
   {
   color:black;
   }
TH
   {
   background-color:#3390CC;
   color:white;
   align=left;
   }
TR.monday
	{
	background-color: #CCFFFF; color: black;
	}
TR.tuesday
	{
	background-color: #FFCCFF; color: black;
	}
TR.wednesday
	{
	background-color: #CCFF99; color: black;
	}
TR.thursday
	{
	background-color: #FFCC66; color: black;
	}
TR.friday
	{
	background-color: #CCFFCC; color: black;
	}
TR.saturday
	{
	background-color: #FFCCCC; color: black;
	}
TR.sunday
	{
	background-color: #99CCFF; color: black;
	}
A:link{color:blue}
A:visited{color:brown}
-->
</STYLE>
<head>
<body>
	<h5>'+ISNULL(@body,'')+'</h5>
	<h5>Source: '+convert(varchar, SERVERPROPERTY('ServerName'))+'.'+db_name()+'.'+@schema+'.'+@table_name+' content '+cast(GETDATE()as varchar(12))+'</h5>
	<table border="1">
'+replace(@headers,'<th> row_css_class </th>','')+@content+'
	</table>
</body>
</html>
'
if @subject like '%ERROR%' 
	set @body=replace(@body,'background-color:#3390CC;','background-color:red;')
	if @send='Y'
	begin
		select 
			@subject=ISNULL(@subject,convert(varchar, SERVERPROPERTY('ServerName'))+' Table '+db_name()+'.'+@schema+'.'+@table_name+' content '+cast(GETDATE()as varchar(12)))
		EXECUTE msdb.dbo.sp_send_dbmail
			@recipients=@email_list,
			@copy_recipients=@cc_list,
			@body_format = 'HTML',
			@body=@body,
			@subject =@subject,
			@profile_name =@mail_profile
	end
	else
		select replace(replace(replace(replace(
				@body,	'<tr>',char(13)+char(10)+char(9)+char(9)+'<tr>'),
						'</tr>',char(13)+char(10)+char(9)+char(9)+'</tr>'),
						'<td>',char(13)+char(10)+char(9)+char(9)+char(9)+'<td>'),
						'<th>',char(9)+char(9)+'<th>')

Open in new window

Then you can use it like this:
declare
	@subject varchar(8000)='Subject here'
select 
	*
	into tmp_for_html_table
from
	...


exec dbo.spHtmlFromTableEmail 
	@mail_profile='Database Mail', 
	@schema='dbo',
	@table_name='tmp_for_html_table',
	--@email_list='your_name@company.com',	/* -- comment this line if you want to send it only to you; uncomment to send it to multiple emails 
	@email_list='email1@company.com;email2@company.com;email3@company.com;...',	--*/
	@cc_list='your_name@company.com',
	@subject=@subject,
	@send='y'
	-- if this parameter is ignored then the default, which is ALL, columns will used; otherwise you can deliver column names separated bu comas
	--,@columns_list='col1,col2,...'

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41852585
In your particular case you will use that stored procedure like this:
declare
	@subject varchar(8000)='Subject here'
if object_id('tmp_for_html_table') is not null
	drop table tmp_for_html_table
select
     cs.SiteName
    ,t.SiteTransDateTime as TranactionDateTime
    ,t.SiteTransID as TransactionID
    ,pt.Type as Identifier
    ,-1 * tp.Amount as Amount
	into tmp_for_html_table
from 
    ClientSite cs
    inner join POS.dbo.Transactions t on cs.SiteID = t.SiteID
    inner join POS.dbo.TransPayment tp on t.TransactionID = tp.TransactionID
    inner join POS.dbo.PaymentType pt on tp.PaymentTypeID = pt.PaymentTypeID
where
    t.SiteTransDateTime > CAST(GETDATE() AS DATE)
    and pt.Type in ('Cash')

Order By TranactionDateTime


exec dbo.spHtmlFromTableEmail 
	@mail_profile='Database Mail', 
	@schema='dbo',
	@table_name='tmp_for_html_table',
	--@email_list='your_name@company.com',	/* -- comment this line if you want to send it only to you; uncomment to send it to multiple emails 
	@email_list='email1@company.com;email2@company.com;email3@company.com;...',	--*/
	@cc_list='your_name@company.com',
	@subject=@subject,
	@send='y'
	-- if this parameter is ignored then the default, which is ALL, columns will used; otherwise you can deliver column names separated bu comas
	--,@columns_list='col1,col2,...'

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41852618
Of course, you will have to replace the mail profile with the one was setup on your server.

In that stored procedure I posted you will see some CSS setings for the days of the week that apply for what I use. You can ignore that, it will work anyway.
0
 
LVL 1

Author Closing Comment

by:hmuser
ID: 41853338
Great that's a big help
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now