CODEWITHSUNDEEP
sqlsql-server-2012sql-agent-job
Roger Dodger
Roger Dodger

Reputation: 987

Generating error full message in email when SQL agent job fail

I have 3 SQL Agent jobs that execute SSIS packages. When the job errors out, it generates an email that has 'The job failed - ' in subject and in the email has a message 'The job failed. The job was invoked by schedule schename, the last step to run was Run_'

It doesn't have the FULL error message that is visible when I view history of the job. Is there any way that the job can be configured to include the COMPLETE error message in the email? Or, is there a way that I can build a stored procedure which will be executed upon a specific job failure, and that queries one of the system tables (sysjobs)?

I've never done it before but I guess I can build a trigger into the sysjobs table that is triggered once a specific job fails, and does a sp_db_sendmail. I'm trying to avoid going this route. Is there an optimal way of accomplishing my task?

Upvotes: 3

Views: 3795

Answers (2)

S3S
S3S

Reputation: 25142

Here is a script that you can use in a job. It looks for any jobs that have failed on the current day. You can run this once a day to see what jobs failed on that day. You can add a time variable if you want to run this every 30 minutes or so and only see the jobs which failed within the last hour. You have to be able to use xp_cmdshell which i assumed you would be able to since you want to use sp_send_dbmail

declare @filepath nvarchar(100)     --file path where results are stored as csv
declare @filename nvarchar(100)     --file name where results are stored
declare @command nvarchar(4000)     --dynamic sql of bcp command
declare @count int                  --result count
declare @emailList varchar(4000)    --people to email
declare @dt int                     --current date in INT format



set @filepath = '"e:\somefolder\'
set @filename = 'Failures.csv"'
set @emailList = '[email protected]; [email protected]'
set @dt = (select convert(int,replace(convert(date,substring(convert(varchar,getdate()),1,11)),'-','')))

--query to get the jobs that failed and why. Looks for jobs that were executed today.
select
    h.run_date,
    h.run_time,
    h.run_status,
    j.name,
    j.description,
    h.message
from 
    msdb.dbo.sysjobhistory  h
    inner join
    msdb.dbo.sysjobs j on j.job_id = h.job_id
where
    h.run_status = 0
    and h.run_date = @dt

--if there were failures then put them in a csv and email them.
set @count = @@ROWCOUNT

if (select @count) > 0
begin
    set @command = 'bcp "select h.run_date, h.run_status, j.name, j.description, h.message from msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j on j.job_id = h.job_id where h.run_status = 0 and h.run_date = convert(int,''' + convert(varchar,@dt) + ''') " queryout '
    set @command = @command + @filepath + @filename + ' -c -t, -T '----> character data, tab delimtted is default use  -t, for comma ... trusted conn,server instance to connect to
    exec master..xp_cmdshell @command

    exec msdb.dbo.sp_send_dbmail 
                    @profile_name = null,
                    @recipients  = @emailList,
                    @body = 'Attached is the job failure results',  
                    @body_format = 'TEXT',
                    @subject = 'Job Failures',
                    @file_attachments = 'e:\someFolder\Failures.csv'

end

Upvotes: 0

Pintu Kawar
Pintu Kawar

Reputation: 2156

One better way is to use multiple job steps.

Step1: Execute Package

If Step1 fails:

Step2: use T-SQL with sp_db_sendmail

Step2 can be customized in many ways like ou can attach the log generated by ssis package of previous step.

Or

Get the Error message from Job history and add to sp_db_sendmail Refer.

If you are using Project deployment model, you can do a lot with catalog like executing ssis package using SP, getting error detail from log tables.

Upvotes: 0

Related Questions