November 19, 2015 at 11:31 am
Hi,
I want to send the result in email in excel format. But the step is failing
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'mailProfile',
@recipients = 'ramana@yahoo.com',
@subject = 'Jobs Run ',
@query = '
select name JobName, t.step_id,t.step_name,max(t.runtime) JobRunTime,
t.RunStatus JobRunStatus from (select sj.job_id,sj.name,sj.enabled, sjh.step_id, sjh.step_name,sjh.message,
DATETIMEFROMPARTS(
run_date / 10000,
(run_date % 10000)/100,
run_date % 100,
run_time / 10000,
(run_time % 10000)/100,
run_time % 100,
0) Runtime
,sjh.run_duration,
sjh.run_status,
CASE WHEN SJH.run_status=0 THEN 'Failed'
WHEN SJH.run_status=1 THEN 'Succeeded'
WHEN SJH.run_status=2 THEN 'Retry'
WHEN SJH.run_status=3 THEN 'Cancelled'
WHEN SJH.run_status=4 THEN 'Running'
else 'NotScheduled' end RunStatus
from msdb.dbo.sysjobs sj
join msdb.dbo.sysjobhistory sjh
on sjh.job_id=sj.job_id) t
left outer join (select
sj.job_id, sjh.step_id,
max(DATETIMEFROMPARTS(
run_date / 10000,
(run_date % 10000)/100,
run_date % 100,
run_time / 10000,
(run_time % 10000)/100,
run_time % 100,
0) ) as maxrundate
from msdb.dbo.sysjobs sj
left outer join msdb.dbo.sysjobhistory sjh
on sjh.job_id=sj.job_id
group by sj.job_id, sjh.step_id
) tm
on t.job_id = tm.job_id and t.runtime = tm.maxrundate
group by name,t.step_id,t.step_name, t.RunStatus
order by JobRunTime desc',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Jobs Run.csv'
November 19, 2015 at 11:36 am
It probably doesn't like the multi line query. Can you make it either a view or a TVF then the @query becomes much simpler.
Yeah looking at it closer you've got sections like
CASE WHEN SJH.run_status=0 THEN 'Failed'
WHEN SJH.run_status=1 THEN 'Succeeded'
WHEN SJH.run_status=2 THEN 'Retry'
WHEN SJH.run_status=3 THEN 'Cancelled'
WHEN SJH.run_status=4 THEN 'Running'
the single quotes will break it.
November 19, 2015 at 12:38 pm
With view it is working.
The format is not in a proper way. I need help in formatting.
I have 5 columns in select list but the jobname, stepid, stepname is coming in one row and job runtime and status are coming in different row.
When I use the below parameters it is coming better but still looks 2 columns result together
@attach_query_result_as_file = 1,
@query_attachment_filename = 'JobsRun.csv',
@query_result_separator=' ',@query_result_width =32767
November 19, 2015 at 3:22 pm
Yes you are right.
I changed the single quotes still it is failing at the same place.
Changed from 'Failed' to [Failed]
November 19, 2015 at 3:50 pm
Try it with @query_result_separator=','
November 19, 2015 at 4:04 pm
It is seperating with comma but I am looking something like as in the excel.
I attached my query results. If we see the image the columns 2(stepId), 3 (stepname) results are looks in one column and columns 4,5 results looks mixed.
November 19, 2015 at 4:58 pm
If "like as in the Excel" you mean that you're looking for a nicely-formatted email, then you may be better off going about it in a different way. Database mail will supports HTML email, so you can create it and email it to yourself, specifying @body_format = 'HTML' in your call to sp_send_dbmail.
You can write your query to wrap <td> and </td> around your cells and <tr> and </tr> around your rows. Then, wrap your HTML and table tags around the it. Define your @body parameter as the HTML you built up and send it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply