December 12, 2018 at 9:20 pm
The following code is below
USE MSDB
Would like to be able to send the email at this point here so then i can automate this job to run about every 30 minutes.
All assistance is greatly appreciated...Thanks
Dheath
DHeath
December 13, 2018 at 12:37 am
Hi,
this is quite simple:
https://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/
Brent Ozar did a good job to explain.
Kind regards,
Andreas
December 13, 2018 at 7:56 am
Thanks for the reply...
Ok..hmmm maybe i didnt do the best job saying what i needed....
I understand how to use the DBMAIL ASPECTS..this part i get fairly good... I am okay here
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Main DBA profile',
@recipients = 'SQLDBA@company.com',
@subject = 'Failed SQL Agent Jobs',
@query = N'SELECT TOP 50
j.[name],
s.step_name,
--h.step_id,
--h.step_name,
h.run_date,
h.run_time,
--h.sql_severity,
-- h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure *****
AND h.run_date > @FinalDate
--ORDER BY h.instance_id
ORDER BY h.run_date DESC ---
CAST(collection_date_time AS Date) = CAST(GETDATE() AS DATE)
ORDER BY collection_date_time DESC;',
@attach_query_result_as_file = 1,@query_attachment_filename = 'Memory Values.txt'
The PROBLEM comes where i try to CAPTURE the results...
SELECT TOP 50
j.[name],
s.step_name,
--h.step_id,
--h.step_name,
h.run_date,
h.run_time,
--h.sql_severity,
-- h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure *****
AND h.run_date > @FinalDate
DHeath
December 16, 2018 at 10:39 am
Why wouldn't you use an INSERT/SELECT into a table and create the email from those results?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 17, 2018 at 7:35 am
Jeff...
First off thank you for the post...much appreciated.
I am not the greatest code writer so i have some problems getting to that point of inserting into the table. I was able to finally get that to happen by using the insert into but then the format of the data was not very good. Yes i could import to an excel table then email but my goal is to make it 100% automated. I did get the notifications to work from "failed" jobs but that doesnt give me all the information i want plus if i have 10 failed jobs then thats 10 emails and trying to make it all one email (IF that makes sense).
I am sure this can be done..its just a matter of me figuring it out LOL
DHeath
DHeath
December 17, 2018 at 9:49 am
I use a version of the query below every M-F:
it returns all jobs who's LAST status is failed.
that means jobs that execute every x minutes,and experienced a hiccup, but ran successfully after wards are ignored.
In my case, i aggregate multiple servers , so i have a consolidated report, so you would need PowerShell or an SSIS package to do that, or run this in a Central Management Servers Query manually to agregate, but for the email from a single server, for all errors,t his would help, i think.
hope this helps:
* edit: modified to now include the email portion.
DECLARE @dt CHAR(8);
SET @dt= CASE
WHEN DATENAME(dw,GETDATE()) = 'Monday'
THEN CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-3), GETDATE())), 112)
ELSE CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)
END
Declare @HTMLBody varchar(max),
@TableHead varchar(max),
@TableTail varchar(max)
Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>Servername</b></td>' +
'<td align=center><b>Step Name</b></td>' +
'<td align=center><b>SQL Job Name</b></td>' +
'<td align=center><b>Failure Date</b></td>' +
'<td align=center><b>RunDateTime</b></td>' +
'<td align=center><b>StepDuration</b></td>' +
'<td align=center><b>ExecutionStatus</b></td>' +
'<td align=center><b>Error Message</b></td></tr>';
Select @HTMLBody = (Select
ROW_NUMBER() Over(Order By CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101)) % 2 As [TRRow],
CONVERT(varchar(128),@@SERVERNAME) As ,
T1.step_name AS ,
SUBSTRING(T2.name,1,140) AS ,
CAST(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101) AS CHAR(11)) AS ,
msdb.dbo.agent_datetime(T1.run_date, T1.run_time) AS ,
T1.run_duration AS ,
CASE T1.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ,
T1.message AS
FROM
msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE
T1.run_status NOT IN (1,2,4)
AND T1.step_id != 0
AND run_date >= @dt
For XML raw('tr'), Elements)
-- Replace the entity codes and row numbers
Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))
Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')
Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')
Select @HTMLBody = @TableHead + @HTMLBody + @TableTail
-- return output
Select @HTMLBody
--email the output:
EXEC msdb.dbo.sp_send_dbmail
--@profile_name='Specific Profile name',
@recipients='lowelle@SomeDomain.com',
@subject = 'DailyErrors',
@body = @HTMLBody,
@body_format = 'HTML'
Lowell
December 17, 2018 at 10:33 am
Lowell,
Thanks a TON!!!! and the formatting is spot on as well GREATLY APPRECIATED. I will use a CMS so that it can hit all servers at once. Thanks again.
DHeath
DHeath
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply