August 12, 2011 at 3:06 am
Hi All,
how to receive a e-mail whenever the sql jobs fails with error message that are logged in sysjobhistory table.
Thanks & Regards
Deepak
August 12, 2011 at 3:18 am
You can E-mail an operator when the job fails (right click the job / properties / Notifications) or you can put error handling in the TSQL within the job step and use sp_send_dbmail example below from BOL.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks Administrator',
@recipients = 'danw@Adventure-Works.com',
@query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder
WHERE DueDate > ''2004-04-30''
AND DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;
Search sp_send_dbmail in BOL.
Thanks
Chris
August 12, 2011 at 3:23 am
hi Chris,
Thanks For your reply.
chris.mcgowan (8/12/2011)
You can E-mail an operator when the job fails (right click the job / properties / Notifications)
Will this provide error information , i mean error message stored in the sys job history table
Regards - Deepak
August 12, 2011 at 4:10 am
The notification through the job properties won't it will just alert that the job has failed. If you build some custom error handling into the TSQL in the job you can capture a wealth of information you could use the below as a starting point and build on it to suit your needs.
IF OBJECT_ID('tempDB.dbo.##JobErrors') IS NOT NULL
DROP TABLE ##JobErrors ;
CREATE TABLE ##JobErrors
(
DatabaseName VARCHAR(50),
ErrorNumber INT,
ErrorSeverity INT,
ErrorState INT,
ErrorLine INT,
ErrorProcedure NVARCHAR(126),
ErrorMessage NVARCHAR(2048)
)
BEGIN TRY
--INSERT SQL HERE
END TRY
BEGIN CATCH
INSERT INTO ##JobErrors
SELECT @DatabaseName AS DatabaseName,
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_LINE() AS ErrorLine,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_MESSAGE() AS ErrorMessage ;
END CATCH
PRINT GETDATE()
IF ( SELECT COUNT(*)
FROM ##JobErrors
) >= 1
BEGIN
PRINT 'ERRORS FROM THE JobErrors TABLE: BEGIN'
SELECT *
FROM ##JobErrors
PRINT 'ERRORS FROM THE JobErrors TABLE: END'
DECLARE @strSubject VARCHAR(70)
SET @strSubject = 'Subject you want here'
EXEC msdb.dbo.sp_send_dbmail @profile_name = '', @recipients = '',
@subject = @strSubject, @query = 'select * from ##JobErrors',
@query_result_width = 2256
END
Thanks
Chris
August 12, 2011 at 5:33 am
hi chris,
i will aggree , if we encounter any error in T-SQL , we will get error message , what if the job invocation itself failed ? and also i have schedule SSIS package ,recently i encountered an error i.e., connection string is wrongly pointed in SSIS Connection manager that is resulted in connection time out error ,how can we manage to get this kind of error ?
Regards - Deepak
August 12, 2011 at 6:15 am
I schedule my jobs to print to an error log for each step and check Include Step Output. For SSIS packages, I use the Operating System job step format (instead of Integration Services) and make sure the step output is included in the log.
This does not email the error to me. Notifications will email me that the job failed and I then go to the error log, open it up, and search for the word "error:" (no quotes). That search takes me to the first error message. Often there are many. Anyway, that Include Step Output is wonderfully detailed and using the Operating System command for SSIS packages gives me more details that most other versions of logging SSIS package runs.
And yes, it will tell me if it can't find a file / connection, though the error seldom is "Your connection manager is wrong". SQL just says it doesn't exist. Close enough, right?
I'm rambling a bit this a.m., so if I didn't make sense, let me know and I'll try to clarify.
August 12, 2011 at 6:23 am
Hi Brandie,
Thanks for your reply,
We receive notifications once a job fails, however, at this time, we need to check the job history/log to figure out why the job fails, and this is time-consuming, so we should find a solution to achieve the following result,
As soon as a job failure notification is received, we should be able to get the job failure reason stored in the job history/log.
Regards - Deepak
August 12, 2011 at 6:44 am
Look at sysjobhistory in msdb. The message column will give you minimal details about failures (run_status = 0). The table seldom has the space to store all the details of failure, which is why you need to search error logs, as much as you don't want to.
August 12, 2011 at 7:04 am
Hi Brandie,
i want to get an email alert including error message that are logged either in the sysjobhisory or error log as soon as error occured. As An automated prcocess.
Regards - Deepak
August 12, 2011 at 7:09 am
Deepak,
No matter what you do, there will always be an element of manual work in this process. Someone will forget to add notifications to the job they create, or the job to the list of jobs you're monitoring, or something.
There is nothing in SQL Server with this information available that could make this easy for you. You will have to figure out a way to create the process with what you've got.
Maybe you can create an SSIS package that executes a few times a day and goes to upload & search the error logs for jobs that failed? I don't know.
Job Failure Notifications is the one thing that I don't 100% trust the automated processes for. Despite our morning notifications (and I have a job that tells me what jobs failed in addition to the job notifications), I always check manually and I always check the error logs manually because the auto-processes always miss something.
August 12, 2011 at 8:25 am
As Brandie said manual work is required to automate.
Create new step and add this to the job you want mail alert for failure. Add the below script to the new step. This step will run when the actual step performing the task fails.
declare @text varchar(max)
select @text= message from sysjobhistory where job_id=(select job_id from sysjobs where name='JOB_NAME')
and run_status=0 order by instance_id desc
declare @date as datetime
declare @servername as varchar(40)
declare @body1 as nvarchar(max)
select @date= getdate()
select @servername= @@servername
select @body1='The job JOB_NAME failed in the server '+ @servername+' on '+ convert(nvarchar(25),@date)+'.' + @text
EXEC sp_send_dbmail @profile_name='dbmail',
@recipients='abc@xyz.com',
@subject='Job failure Mail',
@body=@body1
GO
Ryan
//All our dreams can come true, if we have the courage to pursue them//
August 12, 2011 at 10:45 pm
Hi Sumanta,
Thanks for the reply , i will try this to implement.
Regards-Deepak
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply