How to send mail in if and else statement?

Question

I have this T-SQL statement below. If there are failed jobs, send the email to [e-mail] 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.

Answer 1

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

Answer 2

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 = '[e-mail]',  
    @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 [e-mail].

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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 = '[e-mail]',
	@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]

Answer 7

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

Answer 8

Yes, I can use the HTML tags but how do I remove those dashes lines?

Answer 9

@query_result_header = 0,   
    @query = ....

Answer 10

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!

Answer 11

Okay, I have one additional question, in the Else, I have this:

ELSE
	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Database Mail',
    @recipients = '[e-mail]',
    @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?

Answer 12

Never mind, it works. The email just went to Junk mail box only. Thanks!