September 11, 2013 at 11:06 am
Hi
I have a script, which when ran inserts values from multiple tables into another table, then executes 'sp_send_dbmail'. I use the '@query' parameter in 'sp_send_dbmail' to select from the new table, so that the data that has been amalgamated into the new table is shown in the output when the email is sent. All this works fine.
The issue I am having is to do with how the data is displayed in the email received, the columns and the corresponding data do not align correctly, it's readable but obviously doesn't look great.
For example, The corresponding data for Column A will not align correctly with Column A's heading, Column B's data will not align with Column B's heading etc.
Hope this is clear, can anyone suggest a resolution? It's driving me crazy!
September 11, 2013 at 11:19 am
This is probably because most email clients have a true-type font as their default font and you aren't going to get those columns to line up unless you use tab characters to separate them. Even that's going to be rough because of the variable spacing of characters.
What I've done when emailing query results is to email it as an text file attachment instead of in the body. Notepad uses a fixed-width font, so everything lines up without a lot of extra work. Use the @attach_query_result_as_file parameter and set it to a 1 to do this.
Reference: http://technet.microsoft.com/en-us/library/ms190307%28v=sql.100%29.aspx
September 11, 2013 at 11:44 am
wak_no1 (9/11/2013)
HiI have a script, which when ran inserts values from multiple tables into another table, then executes 'sp_send_dbmail'. I use the '@query' parameter in 'sp_send_dbmail' to select from the new table, so that the data that has been amalgamated into the new table is shown in the output when the email is sent. All this works fine.
The issue I am having is to do with how the data is displayed in the email received, the columns and the corresponding data do not align correctly, it's readable but obviously doesn't look great.
For example, The corresponding data for Column A will not align correctly with Column A's heading, Column B's data will not align with Column B's heading etc.
Hope this is clear, can anyone suggest a resolution? It's driving me crazy!
I am using this code for scheduled job that emails results of a query:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Alert Report</H1>' +
N'<table border="1">' +
N'<tr><th>When Opened</th><th>Resp Area ID</th>' +
N'<th>Resp Area</th><th>Message Key</th><th>Issue</th>' +
CAST ( ( SELECT td = cast(a.[When Opened] as smalldatetime), '',
td = a.RESP_AREA_ID_FK, '',
td = ra.FULL_NAME, '',
td = a.ALERT_KEY, '',
td = a.ISSUE, ''
FROM [DBNAME].dbo.dd_alerts a
LEFT JOIN [DBNAME].dbo.sd_resp_areas ra
ON a.resp_area_id_fk = ra.resp_area_id
WHERE a.ALERT_KEY in('SKIPPED_MSG', 'MANUAL_ADJ_PROD_CYCLES_ADDED')
AND a.[When Opened] > DATEADD(HH,-24,GETDATE())
AND a.RESP_AREA_ID_FK in (1,4,5,6,9,10,11,12,14,28,30,35)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXX_MailMan',
@recipients='batgirl@ssc.com',
@subject = 'Line-side Labeling Alerts',
@body = @tableHTML,
@body_format = 'HTML' ;
September 11, 2013 at 11:44 am
This seems to have done the trick, thanks.
I was looking into HTML formatting, but that looks quite heavy in terms of getting it set-up.
September 11, 2013 at 11:45 am
batgirl (9/11/2013)
wak_no1 (9/11/2013)
HiI have a script, which when ran inserts values from multiple tables into another table, then executes 'sp_send_dbmail'. I use the '@query' parameter in 'sp_send_dbmail' to select from the new table, so that the data that has been amalgamated into the new table is shown in the output when the email is sent. All this works fine.
The issue I am having is to do with how the data is displayed in the email received, the columns and the corresponding data do not align correctly, it's readable but obviously doesn't look great.
For example, The corresponding data for Column A will not align correctly with Column A's heading, Column B's data will not align with Column B's heading etc.
Hope this is clear, can anyone suggest a resolution? It's driving me crazy!
I am using this code for scheduled job that emails results of a query:
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Alert Report</H1>' +
N'<table border="1">' +
N'<tr><th>When Opened</th><th>Resp Area ID</th>' +
N'<th>Resp Area</th><th>Message Key</th><th>Issue</th>' +
CAST ( ( SELECT td = cast(a.[When Opened] as smalldatetime), '',
td = a.RESP_AREA_ID_FK, '',
td = ra.FULL_NAME, '',
td = a.ALERT_KEY, '',
td = a.ISSUE, ''
FROM [DBNAME].dbo.dd_alerts a
LEFT JOIN [DBNAME].dbo.sd_resp_areas ra
ON a.resp_area_id_fk = ra.resp_area_id
WHERE a.ALERT_KEY in('SKIPPED_MSG', 'MANUAL_ADJ_PROD_CYCLES_ADDED')
AND a.[When Opened] > DATEADD(HH,-24,GETDATE())
AND a.RESP_AREA_ID_FK in (1,4,5,6,9,10,11,12,14,28,30,35)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXX_MailMan',
@recipients='batgirl@ssc.com',
@subject = 'Line-side Labeling Alerts',
@body = @tableHTML,
@body_format = 'HTML' ;
Thank you, I will look into this if the client doesn't like what I've come up with 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply