How to setup Email alerts for Reporting jobs when it fails? In SQL server 2008R2, I have a JOB which is running every month 1st week for Reports and I am frequently receiving Error message like "The job failed.  The Job was invoked by Schedule 14. The last step to run was step 1" and Failed step error details: "Message:  Unable to connect to SQL Server '(local)'.  The step failed" Finally I come to know Reports failing because of Database is not fully Restored at the time, when Report schedule starts. After I manually started the Process and Jobs run successfully. 

I wan to know when Report jobs failure and when it happen I need to receive some Email alerts (2 types of Error E-mails needed -  one for Errors and another one for Informational etc..), Can I Setup E-mail alerts for Jobs? 

SQL server Agent Notification and Setting up an Operator is good Idea? or there is any preferable ways to do that?
You are correct that is the best way for individual server. 

Sample code for notification when a job fail.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @jobname=N'test', 
        @notify_level_email=2, 
        @notify_level_netsend=2, 
        @notify_level_page=2, 
        @notify_email_operator_name=N'sysadmin'
GO


You can send email for success/failure or both.


  0 Never
  
  1 On success
  
  2 On failure
  
  3 Always


Books online has details.

There are large shops where instead of setting up notification for each SQL Agent jobs, all failed jobs will be collected at xx minute interval and send one email/raise ticket in the form of a report.