April 14, 2011 at 8:16 am
I have a user who needs to know the details if certain jobs fail; however we do not give him access to SQL Server Agent.
My user receives a notice if the job fails, but has to wait for me to give him the details from the Job History. I’m wondering if there is a way to include those details of the failure in the email notification? Or is there a sys.sp_xxx that he could run that would include the Description line of the error? I have looked through the list in both MSDB and Master and don’t see anything that would help me out.
Any help would be appreciated!!
TIA!
April 15, 2011 at 7:35 am
I had the same problem, so I just added a step to be executed on after any step failure.
This is a TSQL Job step that catches the last job history into a table which I query later and email the details.
I have a modified script but I can't find it, but with this script you can have a better Idea on how to accomplish this...
Since the msdb.dbo.sp_help_jobhistory returns multiple results for the specified job and date, you may want to loop through those results and append them to your email.
Modify it to your needs:
DECLARE @Date INT
SET @Date = CONVERT(INT, CONVERT(CHAR(8), GETDATE(), 112))
CREATE TABLE #JobHistory
(
instance_id INT ,
job_id VARCHAR(50) ,
job_name VARCHAR(35) ,
step_id SMALLINT ,
step_name VARCHAR(50) ,
sql_message_id INT ,
sql_severity INT ,
[message] VARCHAR(2000) ,
run_status INT ,
run_date CHAR(8) ,
run_time CHAR(5) ,
run_duration INT ,
opeator_emailed VARCHAR(10) ,
operator_netsent VARCHAR(10) ,
operator_paged VARCHAR(10) ,
retries_attemped INT ,
serverName VARCHAR(50)
) ;
INSERT INTO #JobHistory
( instance_id ,
job_id ,
job_name ,
step_id ,
step_name ,
sql_message_id ,
sql_severity ,
[message] ,
run_status ,
run_date ,
run_time ,
run_duration ,
opeator_emailed ,
operator_netsent ,
operator_paged ,
retries_attemped ,
serverName
)
EXEC msdb.dbo.sp_help_jobhistory @job_name = 'MyAgentJob',
@mode = 'FULL', @start_run_date = @Date, @run_status = 0,
@step_id = 0
DECLARE @msg VARCHAR(2000)
DECLARE @stepName VARCHAR(75)
DECLARE @jobName VARCHAR(75)
DECLARE @Body VARCHAR(MAX)
DECLARE @Subject VARCHAR(125)
SELECT @msg = [message] ,
@stepName = step_name ,
@jobName = job_name
FROM #JobHistory
DROP TABLE #JobHistory ;
SET @Body = '<strong>JOB NAME: </strong>' + @jobName + '
'
+ '<Strong>STEP NAME: </Strong>' + @stepName + '
'
+ '<Strong>ERROR MESSAGE: </Strong>' + @msg + '
'
SET @Subject = 'Job Failed: ' + @jobName + '\' + @stepName
EXECUTE [msdb].[dbo].[sp_send_dbmail] @profile_name = 'MyDBMailProfile',
@body_format = 'HTML',
@recipients = 'DBAs@mydomain.com',
@body = @Body, @subject = @Subject, @importance = 'High'
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 15, 2011 at 7:43 am
Thank you so much for sharing!
I am off to give it a try.
April 15, 2011 at 3:29 pm
Worked spectacular!!
Thank you again!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply