locked
how to send email notification for Sql server agent jobs RRS feed

  • Question

  • Dears,

    i am running ssis packages through sql agent server job,i want to configure now if the job fails or success send an email notification with error.

    could any one explain me how to send email notifications through sql server agent.

    Regards,


    Sunday, January 6, 2019 7:10 AM

Answers

All replies

  • You can set this up in the job properties

    Just set up database email with profile

    And configure the SQL Server Agent to use it

    Then create an operator with required email addresses in SQL Agent

    Once this is done from every job you can set notifications to send mail to operator when job fails

    The entire steps are explained here

    https://www.sherweb.com/blog/how-to-set-up-database-mail-for-sql-server-job-failures/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Yang.Z Monday, January 7, 2019 2:50 AM
    • Marked as answer by ds_999 Monday, January 7, 2019 6:44 AM
    Sunday, January 6, 2019 7:18 AM
  • Here you go

    https://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Yang.Z Monday, January 7, 2019 2:50 AM
    Sunday, January 6, 2019 8:01 AM
  •  

    thank you visakh,for your quick reply.

    Monday, January 7, 2019 6:44 AM
  • I will say that we use to have a step in each job that on fail it would send an email. Now we have a single SQL Agent job that reports on all failed, cancelled or running steps for the server. The job is scheduled to run at 6:00am, because our nightly processing should be done by then. But this way there is a single job, and a single email that reports on all failed jobs. When sending emails was a step for each job, each developer setup the email differently - there was no consistency. And with each Job you had to include the step.

    Also, we have a table ifs_config that has the configurations for the emails - To Address, Relay Server, IP, etc. And all of our Job names start with the DB Name ... DB Store01 has SQL Agent Jobs Store01_Job1, etc. This proc picks up all the SQL Agent Jobs that start with Store%. So it will send out one email for multiple stores on the same sql server. 

    You might find some of it useful

    /****** Object:  StoredProcedure [dbo].[util_StoreJobCheck]    Script Date: 01/09/2019 15:53:05 ******/
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[util_StoreJobCheck]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[util_StoreJobCheck]
    GO
    
    
    
    
    
    CREATE PROCEDURE [dbo].[util_StoreJobCheck]
    
    /********************************
    
    util_StoreJobCheck - Produces a list of SQL Agent Job steps that (1) did not Succeeded on the last
    run or (2) the SQL Job is currently Running. The jobs must be enabled and either have an enabled
    schedule or the job name is like '%ETLV3'.
    
    The fi_GlobalStoreRef table is read for which DBs to report on. If fi_GLobalStoreRef is empty
    then only the current DB is used.
    
    Information can be PRINT or emailed if an email relay server is setup in ifs_config.Email is only sent 
    out if there is a Failed, Cancelled or Running job.
       
    EXAMPLE EXECUTE
    ===============
    -- do not email, print message only
    exec util_StoreJobCheck @Email=0, @Print=1
    -- email relay server must be setup in ifs_config to email 
    exec util_StoreJobCheck 'brenda.grossnickle@fisglobal.com', 'brenda.grossnickle@fisglobal.com', @IncludeJobMessage = 1
       
    EXAMPLE OUTPUT
    ==============
    SQL Server: ServerBI01
    
    StoreName             DBName JobName              StepName		   StartDateTime       Duration RunStatus 
    --------             ------ -------              ---------		   -------------       -------- --------- 
    Great Store of 03     Store03 Store03_aatest	    BeginParallel_Run  2015-09-15 16:32:01 00:00:00 Failed    
    H360 NAU Store Instal Store40 Store40_EncryptData   EncryptData	   2015-09-15 19:05:00 00:00:03 Failed    
    ACH/EFT/ICM Bk42 201 Store43 Store43_BeBETLV1      Step1			   2015-09-14 09:45:51 00:08:57 Canceled  
                                Store43_BeBETLV1      Step2			   2015-09-14 09:54:53 37:38:43 Running   
    HT Team - TellerRese Store85 Store85_BeBETLV1      Step1			   2015-09-15 15:30:00 00:00:00 Failed
    
    CHANGES1
    =======
    9/25/2015 - bxg - Created 
    2016_01_01 - bxg - change so that any step failures are reported. If ETLV3 fails on the first 
    	step but then the email step succeeds sysjobhistory marks the job as succeed but want to 
    	report on any failed steps. 
    2016_02 - bxg - changed first query to pick up failed and cancelled steps only. under some
    	scenarios it was picking up running also and running was also being picked up in the 
    	second part of the query - so RUnning was listed twice.  
    2016_03 - bxg - returned the failed and current running step name. and increased the email message 
        @varMessage from 1000 to max. It does not really use max. I think there is a limit of 4k or 8k, 
        but 1k is too small.	
    
    **********************************/
    
    	  @FromAddress varchar(250) = '',   -- if not provided uses the ifs_config Email value
    	  @ToAddress varchar(250) = '',     -- if not provided uses the ifs_config email value 
    	  @IncludeJobMessage int = 0,	      -- include the job message
    	  @Email int = 1,			      -- 1 email,
    	  @Print int = 0			      -- 1 Print message
    
    AS
    BEGIN
        SET NOCOUNT ON;
        
        DECLARE @StoreName varchar(40);
        DECLARE @varStoreDB varchar(20);		--The Store DB name
        DECLARE @sql nvarchar(max);			--SQL Statement
    
        DECLARE @RecID int
        DECLARE @JobName varchar(128)
        DECLARE @StepName varchar(128)
        DECLARE @StartDateTime datetime
        DECLARE @RunStatus varchar(25)
        DECLARE @Duration varchar(10)
        DECLARE @Message varchar(400)
        DECLARE @FailedJobs int = 0
    
        DECLARE @varSubject varchar(100);		--Subject line for the email
        DECLARE @varMessage varchar(max);		--Message line for the email
      
        -- if To: is not provided get it from ifs_config
        IF LTRIM(RTRIM(@ToAddress)) = '' or @ToAddress is null
    	   BEGIN
    		  SET @ToAddress = ''
    		  SET @ToAddress = (SELECT TOP 1 textinfo FROM ifs_config where itemname = 'Emails')
    		  IF RTRIM(LTRIM(@ToAddress)) = ''
    			 BEGIN
    				PRINT 'To Email address input parameter was blank and ifs_config Emails is blank. Nothing to do.'
    				RETURN (1)
    			 END
    	   END	  
    
        -- if From: is not provided, then grab off the first To: email address
        IF LTRIM(RTRIM(@FromAddress)) = '' or @FromAddress is null
    	  SET @FromAddress = LEFT(@ToAddress,CHARINDEX(',',REPLACE(@ToAddress,';',',') + ',') - 1)
    
        SET @varSubject = 'Horizon 360 Job Verification';
        SET @varMessage = '';
    
        CREATE TABLE #tmpGlobalStores(StoreDB varchar(20));
    
        CREATE TABLE #tmpDates(LastRunDate varchar(20));
    
        -- Last Run Job Step if Not Succeeded, and currently Running jobs
        CREATE TABLE #Job(RecID int IDENTITY, JobName varchar(128), StepName varchar(128),
    	   StartDateTime datetime, RunStatus varchar(25), Duration varchar(10),
    	   [Message] varchar(600));
    
        INSERT INTO #Job (JobName, StepName, StartDateTime, RunStatus, Duration, [Message])
    	   -- Failed Job Step of last time the job was run. Job must be like Storexx%, enabled, and scheduled or name like %ETLV3
    	   SELECT 
    		  [sJOB].[name] AS [JobName]
    		  , sJobS.step_name as Stepname
    		  , CASE 
    			 WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
    			 ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' ' 
    				    + STUFF( STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
    						  , 3, 0, ':') , 6, 0, ':') AS DATETIME)
    			 END AS [StartDateTime]
    		  , CASE [sJOBH].[run_status]
    			 WHEN 0 THEN 'Failed'
    			 WHEN 1 THEN 'Succeeded'
    			 WHEN 2 THEN 'Retry'
    			 WHEN 3 THEN 'Canceled'
    			 WHEN 4 THEN 'Running' -- In Progress
    			 END AS [RunStatus]
    		  , STUFF(STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
    				    , 3, 0, ':') , 6, 0, ':') AS [Duration]
    		  , Cast([sJOBH].[message] as varchar(600)) AS [Message]
    		  FROM 
    		  [msdb].[dbo].[sysjobs] AS [sJOB]
    		  LEFT JOIN (
    				    SELECT
    					   [job_id]
    					   , [schedule_id]
    					   , MIN([next_run_date]) AS [NextRunDate]
    					   , MIN([next_run_time]) AS [NextRunTime]
    				    FROM [msdb].[dbo].[sysjobschedules]
    				    GROUP BY [job_id], [schedule_id]
    				) AS [sJOBSCH]
    			 ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    		  LEFT JOIN  msdb.dbo.sysschedules AS [sSCH]  
    			 ON sSCH.schedule_id = sJOBSCH.schedule_id      
    		  LEFT JOIN msdb.dbo.sysjobhistory as [sJOBH]
    			 ON sJob.job_id = sJOBH.job_id
    		  LEFT Join msdb.dbo.sysjobsteps as [sJobS]	 
    	           ON sJOBH.job_id = sJobS.job_id 
    			 and sJOBH.step_id = sJobS.step_id			 
    		  INNER JOIN 	 
    			(SELECT job_id, MAX([msdb].[dbo].agent_datetime(run_date, run_time)) AS jobdatetime
    				FROM [msdb].[dbo].sysjobhistory
    					WHERE step_id = 0 
    					GROUP BY job_id) [sJOBH2] 
    			 ON sJob.job_id = sJOBH2.job_id
    		WHERE sJOBH.step_id <> 0 and    -- not the job, just steps
    			sJOBH.[run_status] not in (1,4)  and -- not Succeeded
    		    sJOB.enabled = 1 and        -- Job is enabled
    			(sSCH.enabled = 1	         -- Schedule is enabled or like %ETLV3% (ETLV3 is not on schedule, but is started from another scheduled job)
    		          or [sJOB].[name] like '%ETLV3%') and
    			[msdb].[dbo].agent_datetime(sJOBH.run_date, sJOBH.run_time) >= sJOBH2.jobdatetime  -- only steps from the last time the job ran 
    		--ORDER BY sJOB.name, [msdb].[dbo].agent_datetime(sJOBH.run_date, sJOBH.run_time) desc, sJOBH.step_id desc			         
    			         
    
    	   UNION ALL
    	   	  -- Running Jobs
    		  -- started with https://sqlstudies.com/2013/09/05/a-t-sql-query-to-get-current-job-activity/
    		  -- that way of getting the step name assumes that step1 is always first, then step2, etc. 
    		  -- bxg - made changes for step name. only have last_executed_step_id, which is NULL on first step.
    		  --  so also using start_step_id for NULL else using on_success_step_id to get the next stepname.
    		  SELECT  j.name as JobName, CASE WHEN ja.last_executed_step_id IS NULL THEN js.step_name  ELSE ISNULL(js2.step_name, '') END as StepName,
    			  ja.start_execution_date as StartDateTime, 'Running' AS RunStatus, 
    		   (SELECT RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())/3600), 2) 
    			   + ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())%3600/60), 2) 
    			   + ':' + RIGHT('0' + CONVERT(VARCHAR(2), DATEDIFF(second, start_execution_date, GetDate())%60), 2) ) as Duration,
    			   '' as message
    		   FROM msdb.dbo.sysjobactivity ja 
    			 JOIN msdb.dbo.sysjobs j
    				 ON ja.job_id = j.job_id
    			 LEFT JOIN msdb.dbo.sysjobsteps js  
    				 ON j.job_id = js.job_id
        				 AND ISNULL(ja.last_executed_step_id, j.start_step_id) = js.step_id		    
    			 LEFT JOIN msdb.dbo.sysjobsteps js2
    				 ON js.job_id = js2.job_id
    				 AND js.on_success_step_id = js2.step_id
    		   WHERE ja.session_id = ( SELECT TOP 1 session_id
    							  FROM msdb.dbo.syssessions
    							  ORDER BY agent_start_date DESC )
    		   AND start_execution_date IS NOT NULL
    		   AND stop_execution_date IS NULL
    	   ORDER BY 1,2
    	   
    	   
      
        INSERT INTO #tmpGlobalStores
    	   SELECT Name
    		FROM sys.databases where name like 'Store%';
    	-- if fi_globalStoreRef is not populated, then use current DB
        IF @@RowCount = 0
    	   INSERT INTO #tmpGlobalStores
    		   SELECT DB_NAME(); 			
    
    
        SET @varMessage = 'SQL Server: ' + (SELECT CAST(SERVERPROPERTY('SERVERNAME')AS varchar)) ;
        
        SET @varMessage = @varMessage + CHAR(13) + CHAR(10);
        
    	SET @varMessage = @varMessage + LEFT('StoreName' + REPLICATE(' ',21), 21) ;
    	SET @varMessage = @varMessage + LEFT('DBName' + REPLICATE(' ',7), 7) ;
    	SET @varMessage = @varMessage + LEFT('JobName' + REPLICATE(' ',21), 21) ;
    	SET @varMessage = @varMessage + LEFT('StepName' + REPLICATE(' ',21), 21) ;
    	SET @varMessage = @varMessage + LEFT('StartDateTime' + REPLICATE(' ',20), 20)  ;
    	SET @varMessage = @varMessage + LEFT('Duration' + REPLICATE(' ',9), 9)  ;
    	SET @varMessage = @varMessage + LEFT('RunStatus' + REPLICATE(' ',10), 10) + CHAR(13) + CHAR(10);
    
    	SET @varMessage = @varMessage + LEFT('--------' + REPLICATE(' ',21), 21) ;
    	SET @varMessage = @varMessage + LEFT('------' + REPLICATE(' ',7), 7) ;
    	SET @varMessage = @varMessage + LEFT('-------' + REPLICATE(' ',21), 21);	
    	SET @varMessage = @varMessage + LEFT('--------' + REPLICATE(' ',21), 21);	
    	SET @varMessage = @varMessage + LEFT('-------------' + REPLICATE(' ',20), 20) ;				
    	SET @varMessage = @varMessage + LEFT('--------' + REPLICATE(' ',9), 9);	
    	SET @varMessage = @varMessage + LEFT('---------' + REPLICATE(' ',10), 10) + CHAR(13) + CHAR(10);  
    	
    
        -- Loop through all the Stores through the list
        WHILE(SELECT COUNT(*) FROM #tmpGlobalStores) > 0
    	   BEGIN
    
    		  SELECT TOP 1 @varStoreDB = StoreDB
    		    FROM #tmpGlobalStores order by StoreDB ASC;
    
    		  IF DB_ID(@varStoreDB)IS NULL
    			 GOTO BYPASS;
    
    		  -- ************* Job Agent Reporting
    			
    		  DECLARE job_cursor CURSOR
    			 FOR SELECT RecID, JobName, StepName, StartDateTime, Duration, RunStatus, Message
    				  FROM #Job
    				  WHERE JobName LIKE @varStoreDB + '%' AND JobName <> @varStoreDB + '_JobCheck'
    				  ORDER BY 1,2,3
    
    		  OPEN job_cursor;
    		  FETCH NEXT FROM job_cursor INTO @RecID, @JobName, @StepName, @StartDateTime , @Duration, @RunStatus, @Message
    		
    		  -- Loop through the Failed jobs and Running jobs
    		  WHILE @@FETCH_STATUS = 0
    			 BEGIN
    				  -- get Store name	
    				  SET @StoreName = 'SYSCON table is empty';
    				  SET @sql = 'use ' + @varStoredb + '; select @StoreNameOut = sybknm from syscon;';
    				  EXEC sp_executesql @sql, N'@StoreNameOut varchar(40) OUTPUT', @StoreNameOut = @StoreName OUTPUT;
    				  SET @varMessage = @varMessage + LEFT(LEFT(ISNULL(@StoreName, ''),20) + REPLICATE(' ',21), 21);	
    				  SET @varMessage = @varMessage + @varStoreDB + ' ';
     
    			
    				SET @varMessage = @varMessage + LEFT(ISNULL(@JobName, '') + REPLICATE(' ',20), 20) + ' ' ;	
    				SET @varMessage = @varMessage + LEFT(ISNULL(@StepName, '') + REPLICATE(' ',20), 20) + ' ' ;	
    				
    				IF @StartDateTime IS NULL
    					SET @varMessage = @varMessage + REPLICATE(' ',20) ;				
    				ELSE					
    					SET @varMessage = @varMessage + LEFT(LEFT(CONVERT(varchar, @StartDateTime, 121), 19) + REPLICATE(' ',20), 20) ;				
    				SET @varMessage = @varMessage + LEFT(ISNULL(@Duration, '') + REPLICATE(' ',9), 9);	
    				SET @varMessage = @varMessage + LEFT(ISNULL(@RunStatus, '') + REPLICATE(' ',10), 10) +CHAR(13) + CHAR(10);	
    				IF @IncludeJobMessage = 1
    				IF @IncludeJobMessage = 1 and @RunStatus = 'Failed' 
    					BEGIN
    					--	SET @Message = ISNULL(STUFF(@Message, 1, CASE WHEN CHARINDEX('Started:', @Message) > 0 THEN CHARINDEX('Started:', @Message) - 1 ELSE 0 END, ''), @message)
    						SET @varMessage = @varMessage + 'Message: ' + @Message + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);	
    					END
    				
    				SET @FailedJobs = 1 
    	
    				FETCH NEXT FROM job_cursor INTO @RecID, @JobName, @StepName, @StartDateTime, @Duration, @RunStatus, @Message
    
    			 END;
    
    		  CLOSE job_cursor;
    		  DEALLOCATE job_cursor;
    
    		  BYPASS:
    			
    		  --remove this Store entry and move on to the next
    		  DELETE #tmpGlobalStores
    		    WHERE StoreDB = @varStoreDB;
    
    	   END;
    
        --Send the email	
        IF @FailedJobs = 1
            IF @Email = 1 
    		  EXEC ifs_send_cdosysmail @FromAddress, @ToAddress, @varSubject, @varMessage;
    
        --Print the output	
        IF @Print = 1
    	   PRINT @varMessage;
    
        SET NOCOUNT OFF;
    END;
    
    
    
    
    GO
    
    
    

    Wednesday, January 9, 2019 9:02 PM