March 5, 2012 at 8:06 pm
Comments posted to this topic are about the item Sending Job Failed Information Through E-Mail Using T-SQL
March 6, 2012 at 6:34 am
I would suggest swapping the following:
????@subject????????????=@MSG,
????@body????????????=@SQL,
Such that the @subject =@SQL and @body=@MSG. I find having the job name, server name and failure notice right in the subject would be more helpful. Just my .5 cents.
March 7, 2012 at 7:21 am
Thanks a million. I just implemented this code for two jobs!
March 11, 2012 at 7:31 pm
Does this script reports all failed jobs in one consolidated report for a day in one single email..
March 12, 2012 at 6:10 am
No. You put this into each individual job and if it fails it sends you an email about this job only.
March 19, 2012 at 2:56 am
sqlserver8650 (3/5/2012)
Comments posted to this topic are about the item <A HREF="/scripts/88464/">Sending Job Failed Information Through E-Mail Using T-SQL</A>
Of course it can be in my version there are flaws, but it works perfectly and fits the current objectives of the notification.
DECLARE
@CurrentDate INT,
@Yesterday INT
SET @CurrentDate = CAST(CONVERT(VARCHAR(20), GETDATE(), 112) AS INT)
SET @Yesterday = CAST(CONVERT(VARCHAR(20), GETDATE()-1, 112) AS INT)
CREATE TABLE #Temp2(
instance_id int,
job_id uniqueidentifier,
job_name sysname,
step_id int,
step_name sysname,
sql_message_id int,
sql_severity int,
message nvarchar(max),
run_status int,
run_date int,
run_time int,
run_duration int,
operator_emailed nvarchar(20),
operator_netsent nvarchar(20),
operator_paged nvarchar(20),
retries_attempted int,
server nvarchar(30)
)
SET NOCOUNT ON;
--Read history by jobs full Yesterday And CurrentDay
INSERT INTO #Temp2
EXEC msdb..sp_help_jobhistory @start_run_date = @CurrentDate ,@mode = 'Full'
INSERT INTO #Temp2
EXEC msdb..sp_help_jobhistory @start_run_date = @Yesterday,@mode = 'full'
--In case if the job works once in a minute, but the letter came once a day, make sure that step all the same have error. if corrected himself, he will not send it in a letter.
SELECT
Job_Name,
Step_Name,
Run_Date,
CASE WHEN LEN(Run_Time) = 5 THEN '0'+ LEFT(Run_Time,1) + ':' + LEFT(RIGHT(Run_Time,4),2)+ ':' + RIGHT(Run_Time,2)
ELSE LEFT(Run_Time,2) + ':' + LEFT(RIGHT(Run_Time,4),2)+ ':' + RIGHT(Run_Time,2) END AS RunTime,
Server,
message INTO ##Error
FROM #Temp2 T
WHERE T.Run_Status = 0 AND NOT EXISTS
(
SELECT TOP 1 *
FROM #Temp2 T2
WHERE T2.Job_Name = T.Job_Name AND T2.Step_Name = T.Step_Name AND T2.Run_Date >= T.Run_Date AND T2.Run_Time > T.Run_Time AND T2.Run_Status != 0
)
ORDER BY Job_Name,Step_Id,run_date,run_time DESC
DROP TABLE #Temp2
Declare
@Recipient varchar(500),
@a varchar(50),
@mail varchar(150)
select @a = 'Job failed on '+ @@servername
select @Recipient = 'DBA@mail.ru'
if exists (select TOP 1 * from ##Error)
begin
exec msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName',
@recipients = @Recipient,
@subject = @a,
@query = 'SET NOCOUNT ON;
select * from ##Error',
@query_result_header = 0
DROP TABLE ##Error
end
else
begin
DROP TABLE ##Error
end
March 19, 2012 at 7:56 am
@moiseevb.i
I like your modification.
May 11, 2016 at 6:54 am
Thanks for the post.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply