none
How to send mail in if and else statement? RRS feed

  • Question

  • I have this T-SQL statement below. If there are failed jobs, send the email to myself@mysite.com using Default Profile with Subject: Failed Jobs and in the Body list all the failed jobs. Else if there is no failed jobs, the Body will say "No failed jobs". 

    -- Variable Declarations 
    
    DECLARE @PreviousDate datetime  
    DECLARE @Year VARCHAR(4)   
    DECLARE @Month VARCHAR(2)  
    DECLARE @MonthPre VARCHAR(2)  
    DECLARE @Day VARCHAR(2)  
    DECLARE @DayPre VARCHAR(2)  
    DECLARE @FinalDate INT  
    
    -- Initialize Variables  
    SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day   
    SET @Year = DATEPART(yyyy, @PreviousDate)   
    SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))  
    SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)  
    SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))  
    SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)  
    SET @FinalDate = CAST(@Year + @Month + @Day AS INT)  
    
    -- Final Logic 
    
    SELECT   j.[name],  
             s.step_name,  
             h.step_id,  
             h.step_name,  
             h.run_date,  
             h.run_time,  
             h.sql_severity,  
             h.message,   
             h.server  
    FROM     msdb.dbo.sysjobhistory h  
             INNER JOIN msdb.dbo.sysjobs j  
               ON h.job_id = j.job_id  
             INNER JOIN msdb.dbo.sysjobsteps s  
               ON j.job_id = s.job_id 
               AND h.step_id = s.step_id  
    WHERE    h.run_status = 0 -- Failure  
             AND h.run_date > @FinalDate  
    ORDER BY h.instance_id DESC

    Thanks in advance.

    Monday, December 2, 2019 1:23 PM

Answers

All replies

  • There is notification tab on the left panel of the job where you can send an email on job failure

    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

    Monday, December 2, 2019 1:36 PM
    Answerer
  • Hi,

    Would you like this one ?

    -- Variable Declarations 
    
    DECLARE @PreviousDate datetime  
    DECLARE @Year VARCHAR(4)   
    DECLARE @Month VARCHAR(2)  
    DECLARE @MonthPre VARCHAR(2)  
    DECLARE @Day VARCHAR(2)  
    DECLARE @DayPre VARCHAR(2)  
    DECLARE @FinalDate INT  
    
    -- Initialize Variables  
    SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day   
    SET @Year = DATEPART(yyyy, @PreviousDate)   
    SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))  
    SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)  
    SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))  
    SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)  
    SET @FinalDate = CAST(@Year + @Month + @Day AS INT)  
    
    -- Final Logic 
    if (SELECT   count(*)
    FROM     msdb.dbo.sysjobhistory h  
             INNER JOIN msdb.dbo.sysjobs j  
               ON h.job_id = j.job_id  
             INNER JOIN msdb.dbo.sysjobsteps s  
               ON j.job_id = s.job_id 
               AND h.step_id = s.step_id  
    WHERE    h.run_status = 0 -- Failure  
             AND h.run_date > @FinalDate )>0 
    
    EXEC msdb.dbo.sp_send_dbmail  
        @recipients = 'myself@mysite.com',  
        @body = '
    DECLARE @PreviousDate datetime  
    DECLARE @Year VARCHAR(4)   
    DECLARE @Month VARCHAR(2)  
    DECLARE @MonthPre VARCHAR(2)  
    DECLARE @Day VARCHAR(2)  
    DECLARE @DayPre VARCHAR(2)  
    DECLARE @FinalDate INT  
    
    SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day   
    SET @Year = DATEPART(yyyy, @PreviousDate)   
    SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))  
    SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)  
    SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))  
    SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)  
    SET @FinalDate = CAST(@Year + @Month + @Day AS INT)  
    
    SELECT   j.[name],  
             s.step_name,  
             h.step_id,  
             h.step_name,  
             h.run_date,  
             h.run_time,  
             h.sql_severity,  
             h.message,   
             h.server  
    FROM     msdb.dbo.sysjobhistory h  
             INNER JOIN msdb.dbo.sysjobs j  
               ON h.job_id = j.job_id  
             INNER JOIN msdb.dbo.sysjobsteps s  
               ON j.job_id = s.job_id 
               AND h.step_id = s.step_id  
    WHERE    h.run_status = 0 -- Failure  
             AND h.run_date > @FinalDate  
    ORDER BY h.instance_id DESC',  
        @subject = 'Failed Jobs' 
    
    else print ('No failed jobs')

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 3, 2019 5:54 AM
  • Rachel, many thanks for the help. That's close to what I am looking for. The Body section did not print out the failed jobs though. It just print out the whole section of T-SQL code as is. I assumed that's because it's inside a single quote.
    Tuesday, December 3, 2019 2:05 PM
  • There is notification tab on the left panel of the job where you can send an email on job failure

    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

    I do know about the left panel notification but sometimes I want just one email with all failed jobs instead of individual emails sent separately.
    Tuesday, December 3, 2019 2:07 PM
  • You need to use @query parameter run the query, not assign your query to @body.

    Also you can simplify your date calcuation:

    DECLARE @PreviousDate datetime   
    DECLARE @FinalDate INT  

    SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day   
    select @FinalDate=Cast(format(@PreviousDate,'yyyyMMdd')as INT)

    You can check all parameters for sp_send_dbmail from here:

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver15

    Tuesday, December 3, 2019 3:55 PM
    Moderator
  • If I do this, I got part of the result that I wanted but the format is very goofy.

    -- Variable Declarations 
    
    DECLARE @PreviousDate datetime  
    DECLARE @Year VARCHAR(4)   
    DECLARE @Month VARCHAR(2)  
    DECLARE @MonthPre VARCHAR(2)  
    DECLARE @Day VARCHAR(2)  
    DECLARE @DayPre VARCHAR(2)  
    DECLARE @FinalDate INT  
    DECLARE @Query VARCHAR(2048)
    
    -- Initialize Variables  
    SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day   
    SET @Year = DATEPART(yyyy, @PreviousDate)   
    SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))  
    SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)  
    SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))  
    SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)  
    SET @FinalDate = CAST(@Year + @Month + @Day AS INT) 
    
    -- Final Logic 
    if (SELECT   count(*)
    FROM     msdb.dbo.sysjobhistory h  
             INNER JOIN msdb.dbo.sysjobs j  
               ON h.job_id = j.job_id  
             INNER JOIN msdb.dbo.sysjobsteps s  
               ON j.job_id = s.job_id 
               AND h.step_id = s.step_id  
    WHERE    h.run_status = 0 -- Failure  
             AND h.run_date > @FinalDate )>0 
    
    EXEC msdb.dbo.sp_send_dbmail
    	@profile_name = 'Default Profile',
        @recipients = 'myself@mysite.com',
    	@query = '
    		DECLARE @PreviousDate datetime  
    		DECLARE @Year VARCHAR(4)   
    		DECLARE @Month VARCHAR(2)  
    		DECLARE @MonthPre VARCHAR(2)  
    		DECLARE @Day VARCHAR(2)  
    		DECLARE @DayPre VARCHAR(2)  
    		DECLARE @FinalDate INT  
    
    		SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day   
    		SET @Year = DATEPART(yyyy, @PreviousDate)   
    		SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))  
    		SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)  
    		SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))  
    		SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)  
    		SET @FinalDate = CAST(@Year + @Month + @Day AS INT)  
    
    		SELECT   j.[name],  
    				 s.step_name,  
    				 h.step_id,  
    				 h.step_name,  
    				 h.run_date,  
    				 h.run_time,  
    				 h.sql_severity,  
    				 h.message,   
    				 h.server  
    		FROM     msdb.dbo.sysjobhistory h  
    				 INNER JOIN msdb.dbo.sysjobs j  
    				   ON h.job_id = j.job_id  
    				 INNER JOIN msdb.dbo.sysjobsteps s  
    				   ON j.job_id = s.job_id 
    				   AND h.step_id = s.step_id  
    		WHERE    h.run_status = 0 -- Failure  
    				 AND h.run_date > @FinalDate  
    		ORDER BY h.instance_id DESC',
        @body = @query,
    	@subject = 'Failed Jobs' 
    
    else print ('No failed jobs')


    And the email looks something like this:

    name                                                                                                                             step_name                                                                                                                      
      step_id     step_name                                                                                                                        run_date    run_time    sql_severity message                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                    
                                                                                         server                                                                                                                          
    -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------
    - ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ------------ ----------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------------------
    SQL023 - SSIS - MyApp90                                                                                                      SCCM threat table CSV dump                                                                                                    
                1 SCCM threat table CSV dump                                                                                                          20191203           0            0 Unable to start execution of step 1 (reason: Error authenticating proxy MySite
    \MyApp90, system error: The user name or password is incorrect.).  The step failed.                                                                                                                                                                      

    I only want this part:

    SQL023 - SSIS - MyApp90                                                                                                SCCM threat table CSV dump                                                                                                    
                1 SCCM threat table CSV dump                                                                                                          20191203           0            0 Unable to start execution of step 1 (reason: Error authenticating proxy MySite
    \MyApp90, system error: The user name or password is incorrect.).  The step failed.

    But I want to format so it looks similar to this:

    SQL Server Instance: DB15
    
    12/02/2019 00:05:33: Login failed for user 'MyAdmin'. Reason: Password did not match that for the login provided. [CLIENT: 192.162.1.30]


    Tuesday, December 3, 2019 4:06 PM
  • If I do this, I got part of the result that I wanted but the format is very goofy.

    You will have to form the body of the email by using proper HTML tags, newline char for line break etc. 

    Please see: 
    https://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by Charlie2 Tuesday, December 3, 2019 6:11 PM
    Tuesday, December 3, 2019 4:18 PM
  • Yes, I can use the HTML tags but how do I remove those dashes lines?
    Tuesday, December 3, 2019 4:22 PM
  • @query_result_header = 0,   
        @query = ....
    Tuesday, December 3, 2019 4:47 PM
    Moderator
  • Vaibhav, thanks! With the link you provides, I think I got it working the way I wanted.  Many thanks to those who contributed to this thread, much appreciated!

    Tuesday, December 3, 2019 6:11 PM
  • Okay, I have one additional question, in the Else, I have this:

    ELSE
    	EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Database Mail',
        @recipients = 'myself@mysite.com',
        @body = 'There are no failed jobs',
    	--@body_format = 'HTML',
        @subject = 'DB10 no failed jobs'

    I didn't get any errors but I also did not get any email either. How do I get an email when there is no errors?

    Tuesday, December 3, 2019 7:56 PM
  • Never mind, it works. The email just went to Junk mail box only. Thanks!
    Tuesday, December 3, 2019 8:03 PM