January 2, 2014 at 8:21 am
thanks again Sean,
but I have some date format issue, I tried to convert to ODBC Canonical format(YYYY-MM-DD HH:MI:SS(24h)) but the convert command not working still have(Month DD YYYY HH:MM[AM/PM])
I do something wrong, probably very simple:)
,CONVERT (VARCHAR(19), GETDATE (),120) AS report_date
January 2, 2014 at 8:31 am
You want the date stored this way in the table? If yes, what data type did you define the column as? DATETIME or VARCHAR (19)?
January 2, 2014 at 11:56 pm
I created a datetime column
January 3, 2014 at 5:58 am
You would need to create a VARCHAR (19) column if you want to store it with the style option that you are using. Otherwise store it as DATETIME and retrieve it with the style option for display purposes (recommended).
January 7, 2014 at 6:28 am
thanks again:)
January 7, 2014 at 6:34 am
My pleasure. Did it all work?
January 7, 2014 at 7:58 am
Yes thanks for all the help
fantastic script:)
//Mattias
January 7, 2014 at 8:38 am
Great to hear. 🙂
May 6, 2014 at 3:22 pm
OLD HAND -- or anyone that can help.
Let me start off by saying I am not the greatest tsql writer....
With that said, I modified your script to only use the SQL Agent jobs part and send me an email with failed jobs.
Here is what the code looks like"
----------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
IF OBJECT_ID (N'dbo.usp_FailedSQLAgentJobs_Alert', N'P') IS NULL
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.usp_FailedSQLAgentJobs_Alert AS SELECT 1 AS shell')
END
GO
-----------------------------------------------------------------------------------------------------------------------------
--Main Query: Create Procedure
-----------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.usp_FailedSQLAgentJobs_Alert
@Output_Mode AS CHAR (1) = NULL
,@Recipients AS VARCHAR (MAX) = NULL
,@Copy_Recipients AS VARCHAR (MAX) = NULL
,@Agent_Jobs AS BIT = 1
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
DECLARE @Body AS NVARCHAR (MAX)
DECLARE @Database_ID AS INT
DECLARE @Database_Name_Loop AS NVARCHAR (500)
DECLARE @Date_24_Hours_Ago AS DATETIME
DECLARE @Date_Now AS DATETIME
DECLARE @SQL_Server_Start_Time AS DATETIME
DECLARE @SQL_String AS NVARCHAR (MAX)
DECLARE @Subject AS NVARCHAR (255)
DECLARE @XML_String AS NVARCHAR (MAX)
SET @Body = N''
SET @Date_24_Hours_Ago = GETDATE () - 1
SET @Date_Now = @Date_24_Hours_Ago + 1
SET @Subject = N'~ 001 Failed SQL Agent Jobs: ' + @@SERVERNAME
SET @XML_String = N''
-----------------------------------------------------------------------------------------------------------------------------
--Error Trapping: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_sssr_agent_jobs', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_sssr_agent_jobs
END
-----------------------------------------------------------------------------------------------------------------------------
--Main Query VII: FAILED SQL Server Agent Jobs
-----------------------------------------------------------------------------------------------------------------------------
IF @Agent_Jobs = 0
BEGIN
GOTO Skip_Agent_Jobs
END
SELECT
SJ.name AS job_name
,CONVERT (VARCHAR (19), caLRDT.last_run_date_time, 120) AS last_run_date_time
,(CASE SJH.run_status
WHEN 0 THEN 'Failed'
--WHEN 1 THEN 'Succeeded'
--WHEN 2 THEN 'Retry'
--WHEN 3 THEN 'Canceled'
--WHEN 4 THEN 'In Progress'
END) AS last_status
,(CASE
WHEN SJH.run_duration = 0 THEN '__:__:__'
WHEN LEN (SJH.run_duration) <= 2 THEN '__:__:' + RIGHT ('0' + CONVERT (VARCHAR (2), SJH.run_duration), 2)
WHEN LEN (SJH.run_duration) <= 4 THEN '__:' + STUFF (RIGHT ('0' + CONVERT (VARCHAR (4), SJH.run_duration), 4), 3, 0, ':')
ELSE STUFF (STUFF (RIGHT ('0' + CONVERT (VARCHAR (6), SJH.run_duration), 6), 5, 0, ':'), 3, 0, ':')
END) AS duration
,ISNULL ((CASE
WHEN SJ.[enabled] = 1 THEN CONVERT (VARCHAR (19), sqNRDT.next_run_date_time, 120)
END), '___________________') AS next_run_date_time
,ISNULL ((CASE
WHEN SJ.[enabled] = 1 THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, DATEDIFF (DAY, GETDATE (), sqNRDT.next_run_date_time)), 1)), 4, 23))
END), 'N/A') AS days_away
INTO
dbo.#temp_sssr_agent_jobs
FROM
msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobhistory SJH ON SJH.job_id = SJ.job_id
INNER JOIN
(
SELECT
MAX (XSJ.instance_id) AS instance_id_max
FROM
msdb.dbo.sysjobhistory XSJ
GROUP BY
XSJ.job_id
) sqIIM ON sqIIM.instance_id_max = SJH.instance_id
LEFT JOIN
(
SELECT
SJS.job_id
,MIN (CONVERT (DATETIME, CONVERT (VARCHAR (8), SJS.next_run_date) + ' ' + STUFF (STUFF (RIGHT ('000000' + CONVERT (VARCHAR (6), SJS.next_run_time), 6), 5, 0, ':'), 3, 0, ':'))) AS next_run_date_time
FROM
msdb.dbo.sysjobschedules SJS
INNER JOIN msdb.dbo.sysschedules SS ON SS.schedule_id = SJS.schedule_id
AND SS.[enabled] = 1
WHERE
SJS.next_run_date > 0
GROUP BY
SJS.job_id
) sqNRDT ON sqNRDT.job_id = SJ.job_id
CROSS APPLY
(
SELECT
CONVERT (DATETIME, CONVERT (VARCHAR (8), SJH.run_date) + ' ' + STUFF (STUFF (RIGHT ('000000' + CONVERT (VARCHAR (6), SJH.run_time), 6), 5, 0, ':'), 3, 0, ':')) AS last_run_date_time
) caLRDT
WHERE
caLRDT.last_run_date_time >= @Date_24_Hours_Ago
IF @@ROWCOUNT = 0
BEGIN
GOTO Skip_Agent_Jobs
END
-----
----
IF @Output_Mode = 'E'
BEGIN
SET @XML_String =
CONVERT (NVARCHAR (MAX),
(
SELECT
'',X.job_name AS 'td'
,'',X.last_run_date_time AS 'td'
,'',X.last_status AS 'td'
--,'',X.duration AS 'td'
--,'',X.next_run_date_time AS 'td'
--,'','right_align' + X.days_away AS 'td'
FROM
dbo.#temp_sssr_agent_jobs X
WHERE X.last_status = 'Failed'
--AND X.last_run_date_time < DATEADD(hh,-2.5,getdate())
ORDER BY
X.job_name
FOR
XML PATH ('tr')
)
)
SET @Body = @Body +
N'
<h3><center> FAILED SQL Server Agent Jobs </center></h3>
<center>
<table border=1 cellpadding=2>
<tr>
<th>Job Name</th>
<th>Last Run Date / Time</th>
<th>Last Status</th>
</tr>
'
SET @Body = @Body + @XML_String +
N'
</table>
</center>
'
END
ELSE BEGIN
SELECT
X.job_name
,X.last_run_date_time
,X.last_status
FROM
dbo.#temp_sssr_agent_jobs X
WHERE X.last_status = 'Failed'
ORDER BY
X.job_name
END
Skip_Agent_Jobs:
IF OBJECT_ID (N'tempdb.dbo.#temp_sssr_agent_jobs', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_sssr_agent_jobs
END
-----------------------------------------------------------------------------------------------------------------------------
--Variable Update: Finalize @Body Variable Contents
-----------------------------------------------------------------------------------------------------------------------------
IF @Output_Mode = 'E'
BEGIN
SET @Body =
N'
<html>
<body>
<style type="text/css">
table {font-size:8.0pt;font-family:Arial;text-align:left;}
tr {text-align:left;}
</style>
'
+ @Body +
N'
</body>
</html>
'
SET @Body = REPLACE (@Body, N'<td>right_align', N'<td align="right">')
END
-----------------------------------------------------------------------------------------------------------------------------
--sp_send_dbmail: Deliver Results / Notification To End User(s)
-----------------------------------------------------------------------------------------------------------------------------
IF @Output_Mode = 'E'
BEGIN
EXECUTE msdb.dbo.sp_send_dbmail
@recipients = @Recipients
,@copy_recipients = @Copy_Recipients
,@subject = @Subject
,@body = @Body
,@body_format = 'HTML'
END
GO
----------------------------
I scheduled the job and it emails me failed jobs. But how can I stop the emails from coming when there are no failed jobs?
JOE
May 6, 2014 at 3:40 pm
Have you tried moving the "Skip_Agent_Jobs:" to the bottom of the script, right before the final "GO"? Or even just setting up each job to notify you individually if / when they fail?
May 6, 2014 at 3:53 pm
I am not sure what you mean.
I tried moving it and error out.
May 6, 2014 at 4:01 pm
Try this:
----------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
IF OBJECT_ID (N'dbo.usp_FailedSQLAgentJobs_Alert', N'P') IS NULL
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.usp_FailedSQLAgentJobs_Alert AS SELECT 1 AS shell')
END
GO
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Create Procedure
-----------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.usp_FailedSQLAgentJobs_Alert
@Output_Mode AS CHAR (1) = NULL
,@Recipients AS VARCHAR (MAX) = NULL
,@Copy_Recipients AS VARCHAR (MAX) = NULL
,@Agent_Jobs AS BIT = 1
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
DECLARE @Body AS NVARCHAR (MAX)
DECLARE @Database_ID AS INT
DECLARE @Database_Name_Loop AS NVARCHAR (500)
DECLARE @Date_24_Hours_Ago AS DATETIME
DECLARE @Date_Now AS DATETIME
DECLARE @SQL_Server_Start_Time AS DATETIME
DECLARE @SQL_String AS NVARCHAR (MAX)
DECLARE @Subject AS NVARCHAR (255)
DECLARE @XML_String AS NVARCHAR (MAX)
SET @Body = N''
SET @Date_24_Hours_Ago = GETDATE () - 1
SET @Date_Now = @Date_24_Hours_Ago + 1
SET @Subject = N'~ 001 Failed SQL Agent Jobs: ' + @@SERVERNAME
SET @XML_String = N''
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_sssr_agent_jobs', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_sssr_agent_jobs
END
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query VII: FAILED SQL Server Agent Jobs
-----------------------------------------------------------------------------------------------------------------------------
IF @Agent_Jobs = 0
BEGIN
GOTO Skip_Agent_Jobs
END
SELECT
SJ.name AS job_name
,CONVERT (VARCHAR (19), caLRDT.last_run_date_time, 120) AS last_run_date_time
,(CASE SJH.run_status
WHEN 0 THEN 'Failed'
--WHEN 1 THEN 'Succeeded'
--WHEN 2 THEN 'Retry'
--WHEN 3 THEN 'Canceled'
--WHEN 4 THEN 'In Progress'
END) AS last_status
,(CASE
WHEN SJH.run_duration = 0 THEN '__:__:__'
WHEN LEN (SJH.run_duration) <= 2 THEN '__:__:' + RIGHT ('0' + CONVERT (VARCHAR (2), SJH.run_duration), 2)
WHEN LEN (SJH.run_duration) <= 4 THEN '__:' + STUFF (RIGHT ('0' + CONVERT (VARCHAR (4), SJH.run_duration), 4), 3, 0, ':')
ELSE STUFF (STUFF (RIGHT ('0' + CONVERT (VARCHAR (6), SJH.run_duration), 6), 5, 0, ':'), 3, 0, ':')
END) AS duration
,ISNULL ((CASE
WHEN SJ.[enabled] = 1 THEN CONVERT (VARCHAR (19), sqNRDT.next_run_date_time, 120)
END), '___________________') AS next_run_date_time
,ISNULL ((CASE
WHEN SJ.[enabled] = 1 THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, DATEDIFF (DAY, GETDATE (), sqNRDT.next_run_date_time)), 1)), 4, 23))
END), 'N/A') AS days_away
INTO
dbo.#temp_sssr_agent_jobs
FROM
msdb.dbo.sysjobs SJ
INNER JOIN msdb.dbo.sysjobhistory SJH ON SJH.job_id = SJ.job_id
INNER JOIN
(
SELECT
MAX (XSJ.instance_id) AS instance_id_max
FROM
msdb.dbo.sysjobhistory XSJ
GROUP BY
XSJ.job_id
) sqIIM ON sqIIM.instance_id_max = SJH.instance_id
LEFT JOIN
(
SELECT
SJS.job_id
,MIN (CONVERT (DATETIME, CONVERT (VARCHAR (8), SJS.next_run_date) + ' ' + STUFF (STUFF (RIGHT ('000000' + CONVERT (VARCHAR (6), SJS.next_run_time), 6), 5, 0, ':'), 3, 0, ':'))) AS next_run_date_time
FROM
msdb.dbo.sysjobschedules SJS
INNER JOIN msdb.dbo.sysschedules SS ON SS.schedule_id = SJS.schedule_id
AND SS.[enabled] = 1
WHERE
SJS.next_run_date > 0
GROUP BY
SJS.job_id
) sqNRDT ON sqNRDT.job_id = SJ.job_id
CROSS APPLY
(
SELECT
CONVERT (DATETIME, CONVERT (VARCHAR (8), SJH.run_date) + ' ' + STUFF (STUFF (RIGHT ('000000' + CONVERT (VARCHAR (6), SJH.run_time), 6), 5, 0, ':'), 3, 0, ':')) AS last_run_date_time
) caLRDT
WHERE
caLRDT.last_run_date_time >= @Date_24_Hours_Ago
AND SJH.run_status = 0
IF @@ROWCOUNT = 0
BEGIN
GOTO Skip_Agent_Jobs
END
IF @Output_Mode = 'E'
BEGIN
SET @XML_String =
CONVERT (NVARCHAR (MAX),
(
SELECT
'',X.job_name AS 'td'
,'',X.last_run_date_time AS 'td'
,'',X.last_status AS 'td'
--,'',X.duration AS 'td'
--,'',X.next_run_date_time AS 'td'
--,'','right_align' + X.days_away AS 'td'
FROM
dbo.#temp_sssr_agent_jobs X
WHERE X.last_status = 'Failed'
--AND X.last_run_date_time < DATEADD(hh,-2.5,getdate())
ORDER BY
X.job_name
FOR
XML PATH ('tr')
)
)
SET @Body = @Body +
N'
<h3><center> FAILED SQL Server Agent Jobs </center></h3>
<center>
<table border=1 cellpadding=2>
<tr>
<th>Job Name</th>
<th>Last Run Date / Time</th>
<th>Last Status</th>
</tr>
'
SET @Body = @Body + @XML_String +
N'
</table>
</center>
'
END
ELSE BEGIN
SELECT
X.job_name
,X.last_run_date_time
,X.last_status
FROM
dbo.#temp_sssr_agent_jobs X
WHERE X.last_status = 'Failed'
ORDER BY
X.job_name
END
-----------------------------------------------------------------------------------------------------------------------------
-- Variable Update: Finalize @Body Variable Contents
-----------------------------------------------------------------------------------------------------------------------------
IF @Output_Mode = 'E'
BEGIN
SET @Body =
N'
<html>
<body>
<style type="text/css">
table {font-size:8.0pt;font-family:Arial;text-align:left;}
tr {text-align:left;}
</style>
'
+ @Body +
N'
</body>
</html>
'
SET @Body = REPLACE (@Body, N'<td>right_align', N'<td align="right">')
END
-----------------------------------------------------------------------------------------------------------------------------
-- sp_send_dbmail: Deliver Results / Notification To End User(s)
-----------------------------------------------------------------------------------------------------------------------------
IF @Output_Mode = 'E'
BEGIN
EXECUTE msdb.dbo.sp_send_dbmail
@recipients = @Recipients
,@copy_recipients = @Copy_Recipients
,@subject = @Subject
,@body = @Body
,@body_format = 'HTML'
END
Skip_Agent_Jobs:
IF OBJECT_ID (N'tempdb.dbo.#temp_sssr_agent_jobs', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_sssr_agent_jobs
END
GO
May 6, 2014 at 4:06 pm
It didn't work..
It still sent an email but the email was blank because no jobs failed.
I hoping to avoid blank emails when no jobs fail.
May 6, 2014 at 4:36 pm
Sorry, I did a post and then an edit. Does the version you are trying have "AND SJH.run_status = 0" in it?
May 6, 2014 at 4:42 pm
Sean Smith-776614 (5/6/2014)
Sorry, I did a post and then an edit. Does the version you are trying have "AND SJH.run_status = 0" in it?
Looks like it working.. I must have gotten the un edited code first.
THANK YOU VERY MUCH.
Joe
Viewing 15 posts - 151 through 165 (of 189 total)
You must be logged in to reply to this topic. Login to reply