Solved

Email Query Results in HTML Using a SQL Server Agent Job

Posted on 2016-10-20
4
30 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

929 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

10 Experts available now in Live!

Get 1:1 Help Now