December 10, 2008 at 2:43 am
Hello All,
I have created a job in SQL2005 what runs a SP and emails the results using 'sp_send_dbmail'
Problem
How can format the email to be more appealing? currently it is just standard text.
i.e. How could I add:
- font to selected sections?
- Maybe images (A banner at the top of the email)
I have seen the following example:
EXEC master.dbo.xp_smtp_sendmail
@server = 'SMTP.UK.CORP.Company.COM',
@from = 'sqladmin@company.co.uk',
@to = 'Paul.Smith@company.co.uk',
@subject = 'HTML Testing...',
@type = 'text/html',
@body =
But as far as i can see this is just a standard email with HTML formatting, I want to format the out come of my SP....
@query='exec procByAllSector'
Has any one found any good tutorials online?
Thanks
December 10, 2008 at 2:56 am
How can format the email to be more appealing? currently it is just standard text.
i.e. How could I add:
- font to selected sections?
- Maybe images (A banner at the top of the email)
Do you want to send the email on demand or periodically?
If you want to sent the email periodically, create a report using SSRS, and create a subscription to email the report as PDF.
December 10, 2008 at 3:00 am
The email would be sent periodically every morning.
Nice idea, but the results have to be imbedded within the body of the email. (e.g. HTML Email)
i.e. not an attachment
December 11, 2008 at 12:32 pm
Try using FOR XML PATH and creating an HTML table for the data.
Here's some code that I use to report the status of scheduled jobs that ran the previous day. The code runs in a scheduled job every morning and emails me the results in an HTML table
(disclaimer: I found some of the code online and amended it to suit my own purpose, so this is not all my own work!)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
-- status
LTRIM(RTRIM(CASE WHEN jh.run_status = 1 THEN 'Successful' Else 'Failed' END)) AS 'td'
,'' -- formatting spacer
-- last run date
,LTRIM(RTRIM(RIGHT(jh.run_date,2) +' '+
DATENAME(MONTH, CONVERT(DATETIME, LEFT(jh.run_date,4) +'-'+
RIGHT(LEFT(jh.run_date,6),2)+'-'+
RIGHT(jh.run_date,2), 102))+' '+
LEFT(jh.run_date,4) )) AS 'td'
,'' -- formatting spacer
-- job name
,LTRIM(RTRIM(j.[name])) AS 'td'
FROM msdb..sysjobhistory jh
INNER JOIN msdb..sysjobs j
ON j.job_id = jh.job_id
INNER JOIN msdb..sysjobschedules js
ON j.job_id = js.job_id
-- build up date by addition, to return all jobs FROM previous 24 hours
-- get year from yesterday's date then multiply by 1000 (e.g. 2008000)
WHERE run_date >= DATEPART(YEAR, GETDATE()-1)*10000+
-- get month number and multiply by 100 (0400) add to YEAR (20080400)
DATEPART(MONTH, GETDATE()-1)*100+
-- get day number (27) and add to year-month (20080427)
DATEPART(DAY, GETDATE()-1) -- change this value to see earlier records
AND step_id = 0
ORDER BY jh.run_status
,[name]
,j.job_id
, jh.step_id
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
-- highlight any failed jobs with red background
SELECT @xml = REPLACE(@xml,' ')
-- get name of SQL Server
DECLARE @server VARCHAR(5)
SELECT @server = [server] FROM msdb..sysjobhistory
SET @body ='
'
SET @body = @body + @xml +' '
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'abc@xyz.com'-- <<----- amend recipient list as required. for multiple reipients, use ";" as a separator
,@body = @body
,@body_format ='HTML'
,@subject ='Scheduled Jobs Status'
,@profile_name ='xxxx'
December 12, 2008 at 1:14 am
Thanks looks good.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply