Ever wanted to take a table, permanent or temporary or a view and just email it straight from SQL where the query result sits in the email as a nicely formatted HTML table rather than that ugly text file attachment you get when using sp_send_dbmail? Ever hit that infinite deadlock I blogged about the other day if you use the sp_send_dbmail attachment feature? Well, this stored procedure is for you!
This is an expansion of an example in the sp_send_dbmail section in books online, all I’ve done is made it dynamically accept a table instead of you needing to hard code it.
The stored procedure is very basic and I’ll probably expand it considerably for a production environment (Monday Darren! - honest), you pass in the source database, schema and table or view name of the object you want to convert into a HTML table in your email.
Usage is simple; if you have a complex query just run it into a temporary table and pass that into the procedure.
Example
select type, cnt = count(*)
into #toemail
from sys.objects
group by type
exec email_object_as_html @source_db = 'tempdb',
@schema = '',
@object_name = '#toemail',
@order_clause = 'cnt desc, type',
@email = 'tonyrogerson@torver.net'
drop table #toemail
Sends an email as follows:-
Below is the table tempdb..#toemail converted from a query into HTML for your pleasure
| type |
cnt |
| S |
41 |
| U |
16 |
| IT |
3 |
| SQ |
3 |
| AF |
1 |
| P |
1 |
| PC |
1 |
Anyway have a play, usual provisos because I’m using dynamic SQL, I’ve not put the injection protection in this because I wanted to keep the proc in its simplest form for the blog. HTML HT
create proc email_object_as_html
@source_db sysname, -- Where the @object_name is resident
@schema sysname, -- Schema name eg.. dbo.
@object_name sysname, -- Table or view to email
@order_clause nvarchar(max), -- The order by clause eg. x, y, z
@email nvarchar(max) -- Email recipient list
as
begin
/**
Convert's the specified table or view into an html table and emails it.
Tony Rogerson, SQL Server MVP
28 March 2008
http://sqlblogcasts.com/blogs/tonyrogerson
You are free to use and modify this,
just keep the above in place;
I offer no warranties, you get this as is.
**/
declare @subject nvarchar(max),
@body nvarchar(max)
-- Get columns for table headers..
exec( '
declare col_cur cursor for
select name
from ' + @source_db + '.sys.columns
where object_id = object_id( ''' + @source_db + '.' + @schema + '.' + @object_name + ''')
order by column_id
' )
open col_cur
declare @col_name sysname
declare @col_list nvarchar(max)
fetch next from col_cur into @col_name
set @body = N'<table border=1 cellpadding=1 cellspacing=1><tr>'
while @@fetch_status = 0
begin
set @body = cast( @body as nvarchar(max) )
+ N'<th>' + @col_name + '</th>'
set @col_list = coalesce( @col_list + ',', '' ) + ' td = ' + cast( @col_name as nvarchar(max) ) + ', '''''
fetch next from col_cur into @col_name
end
deallocate col_cur
set @body = cast( @body as nvarchar(max) )
+ '</tr>'
declare @query_result nvarchar(max)
declare @nsql nvarchar(max)
-- Form the query, use XML PATH to get the HTML
set @nsql = '
select @qr =
cast( ( select ' + cast( @col_list as nvarchar(max) )+ '
from ' + @source_db + '.' + @schema + '.' + @object_name + '
order by ' + @order_clause + '
for xml path( ''tr'' ), type
) as nvarchar(max) )'
exec sp_executesql @nsql, N'@qr nvarchar(max) output', @query_result output
set @body = cast( @body as nvarchar(max) )
+ @query_result
-- Send notification
set @subject = 'Your table as requested'
set @body = @body + cast( '</table>' as nvarchar(max) )
set @body = '<p>Below is the table ' + @source_db + '.' + @schema + '.' + @object_name
+ ' converted from a query into HTML for your pleasure</p>'
+ cast( @body as nvarchar(max) )
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'TORVER',
@recipients = @email,
@body = @body,
@body_format = 'HTML',
@subject = @subject
end