SQL Mail Configuration

• arooj300

  Hall of Fame

  Points: 3433

  More actions

  September 8, 2014 at 3:57 am

  #293007

  Hi,

  I have configured DB mail for running job. I am getting daily mail whenever job is running in both cases either success or fail.

  Whenever job is failed , in the mail I am not able to find what is the reason for job failure. For this situation I have to go the particular server and find the failure job and chack manually why it is failed.

  My question is , Instead of checking manually , Is there anyway to get details in the mail itself.

  Thanks in advance..

• Dhruva_51

  SSChampion

  Points: 10521

  More actions

  September 9, 2014 at 10:09 am

  #1744180

  Hi,

  Create a SP using the below link

  http://www.sqlservercentral.com/articles/SQL+Server+Agent/67726/

  then use the sp_send_dbmail to send the job history when the job fails.

  as below:

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'DBMail_BAP',-- provide your profile name

  @execute_query_database = 'msdb',

  @recipients = 'jxxxxx@corp.com',

  @query = 'EXEC dbo.pr_GetStepFailureData ''provideyourJobname''' ,

  @subject = 'xxxx job has been failed',

  @exclude_query_output = 1,

  @append_query_error = 1