SQL Job outputting as HTML Table instead of text

Question

Ok so i have a job that runs with 4 separate select queries in it.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Database Email',
    @recipients = 'dbcheck@xxx.com',
    @subject = 'SQL Replication Deletion Check',
    @query = ''SELECT '\\server1\folder1\SQL\check.sql''
SELECT '' ''
SELECT ''1''
SELECT * FROM [repserv].[dbo].[1repdupecheck]
SELECT '' ''
SELECT ''2''
SELECT * FROM [repserv].[dbo].[2repdupecheck]
SELECT '' ''
SELECT ''3''
SELECT * FROM [repserv].[dbo].[3repdupecheck]
SELECT '' ''
SELECT ''4''
SELECT * FROM [repserv].[dbo].[4repdupecheck]',
    @attach_query_result_as_file = 0,
    @query_result_no_padding = 1,
    @query_result_header = 0;

Currently it outputs as text quite nicely like so.

\\server1\folder1\SQL\check.sql

1
1820 '45074',
1854 '45074',
1822 '45488',

2
1819 '45074',
1853 '45074',
1851 '45488',

3
1871 '43971',
1873 '43971',
1875 '44429',

4
1842 '42729',
1857 '42729',
1870 '43971',

However I would like it to be in table format so I can highlight a column of data at a time easily.

I understand this can be done with HTML, is this the only way possible, if so how would i go about doing this?

score 1 · Accepted Answer · answered Apr 03 '14 at 14:19

The way that I typically do this is to put these results into the @body parameter, and not the @query parameter. That is, I first get all of the results and format them, and then simply pass that to the procedure, instead of doing it on the fly at the procedure call.

This might look something like this:

DECLARE @EmailBody NVARCHAR(MAX) = 'Some Text Here <table><thead><tr><th>SomeHeaderOne</th><th>SomeHeaderTwo</th></tr></thead><tbody>##TableBody##</tbody>'
DECLARE @EmailTableContent NVARCHAR(MAX)

SELECT @EmailTableContent = COALESCE(@EmailTableContent + '', '')
                        + '<tr>'
                        + '<td style="border-bottom: solid 1px #ddd; padding: 20px;">' + ColumnOne + '</td>'
                        + '<td style="border-bottom: solid 1px #ddd; padding: 20px; text-align: center;">' + ColumnTwo + '</td>'
                        + '</tr>'
FROM TheTable
WHERE ColumnOne = 'Something'

SELECT @EmailBody = REPLACE(@EmailBody, '##TableBody##', @EmailTableContent)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Database Email',
@recipients = 'dbcheck@xxx.com',
@subject = 'SQL Replication Deletion Check',
@body = @EmailBody,

Tried to do this with the above code but couldn't get it to work at all, not quite sure what goes where etc — Matt, Apr 04 '14 at 10:25
Well, you have `@EmailBody` which defines the structure of the email and the table. Then, `@EmailTableContent` which is generates an HTML string that is a `` for each row, where you'd change `ColumnOne` and `ColumnTwo`; you'd change `FROM TheTable` to `FROM [repserv].[dbo].[1repdupecheck]`. You will have to do it four times since you want four different tables, maybe have four different different variables. Then, you inject that generated HTML into the @EmailBody, and execute the procedure as shown. — Tom, Apr 04 '14 at 13:24

SQL Job outputting as HTML Table instead of text

1 Answers1