Answered by:
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 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.
Answers
-
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
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
-
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. -
-
There is notification tab on the left panel of the job where you can send an email on job failure
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.
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
-
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:
-
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]
-
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
-
-
-
-
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?
-