The below code is sending an empty email if the condition fails. But I should not receive an email if the condition fails.Should only receive an email if there are records in this table : #tlogtables
No records no email is what needs to happen. Thanks
SET NOCOUNT ON
DECLARE @threshold INT = 99
DECLARE @body nvarchar(max)
CREATE TABLE #tlogtables
(
databaseName sysname,
logSize DECIMAL (18, 5),
logUsed DECIMAL (18, 5),
status INT
)
INSERT INTO #tlogtables
EXECUTE ('DBCC SQLPERF(LOGSPACE)')
-- step_3: get T-logs exceeding threshold size for a specific database
SELECT databaseName,
logSize,
logUsed,
status
FROM #tlogtables
WHERE logUsed >= (@threshold) --AND databaseName in ('dbMaintenance','ReportServerTempDB')
SET @body = N'
<style type=''text/css''>
TABLE{border-width: 1px;border-style: solid;background-color: #E8E8E8; border-color: black;border-collapse: collapse;}
TH{color:black; border-width: 1px;font-size: 11px; padding: 3px;border-style: solid;border-color: black;}
TD{color:black; border-width: 1px;font-size: 11px; padding: 3px;border-style: solid;border-color: black;}
</style>' +
N'
' +
N'<table>' +
N' <th> databaseName </th> <th> logSize </th> <th> PercentlogUsed </th> <th> stats </th>' +
CAST((select databaseName AS 'td','', logSize AS 'td','', logUsed AS 'td','',
status AS 'td',''
FROM #tlogtables
WHERE logUsed >= (@threshold) --AND databaseName in ('dbMaintenance','ReportServerTempDB')
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) +
N'</table>'
IF (SELECT COUNT(*) FROM #tlogtables) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'abc@oracle.com',
@subject = 'Low Log Space Report',
@body = @body,
@body_format ='HTML',
@importance = 'High'
END
DROP TABLE #tlogtables
SET NOCOUNT OFF
August 6, 2024 at 5:09 am
You need to put a count or if exists condition around the mail part. So that if there’s no rows which match the select used to generate the html it doesn’t send the mail.
The current one just checks if the table is not empty but you will always have rows due to the system dbs etc.
I tried this ..... but it still sends email if there are no records...
IF @@rowcount > 0
--IF (SELECT COUNT(*) FROM #tlogtables) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
Also tried this...
IF (SELECT COUNT(*) FROM #tlogtables) > 0
BEGIN
SET @Cnt=1
END
IF @Cnt=1
BEGIN
--IF (SELECT COUNT(*) FROM #tlogtables) > 0
EXEC msdb.dbo.sp_send_dbmail
.....
Still get an email if there are no records..
August 7, 2024 at 11:54 am
Oh boy.
Seriously the logic will always give you rows.
If you check @@ROWCOUNT after setting @BODY you will have a rowcount of 1
If you check COUNT(*) from #temp table you will always have rows.
Of check if @body is populated with some value before sending the mail
The logic you're imploring isn't valid, you need to work on fixing the IF logic.
Examples would be
You need to put your logic check into the COUNT, to see if there is any rows which match the check
IF (SELECT COUNT(*) FROM #tlogtables WHERE logused >= @threashold) > 0
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'abc@oracle.com',
@subject = 'Low Log Space Report',
@body = @body,
@body_format ='HTML',
@importance = 'High'
END
DROP TABLE #tlogtables
SET NOCOUNT OFF
Even better would be to not even bother setting @body unless there was something to set it too, so move the check above the set @body
SET NOCOUNT ON
DECLARE @threshold INT = 99
DECLARE @body nvarchar(max)
CREATE TABLE #tlogtables
(
databaseName sysname,
logSize DECIMAL (18, 5),
logUsed DECIMAL (18, 5),
status INT
)
INSERT INTO #tlogtables
EXECUTE ('DBCC SQLPERF(LOGSPACE)')
IF (SELECT COUNT(*) FROM #tlogtables WHERE logused >= @threshold) > 0
BEGIN
SET @body = N'
<style type=''text/css''>
TABLE{border-width: 1px;border-style: solid;background-color: #E8E8E8; border-color: black;border-collapse: collapse;}
TH{color:black; border-width: 1px;font-size: 11px; padding: 3px;border-style: solid;border-color: black;}
TD{color:black; border-width: 1px;font-size: 11px; padding: 3px;border-style: solid;border-color: black;}
</style>' +
N'
*** This is an automatic email, please do not reply ***
' +
N'<table>' +
N' <th> databaseName </th> <th> logSize </th> <th> PercentlogUsed </th> <th> stats </th>' +
CAST((select databaseName AS 'td','', logSize AS 'td','', logUsed AS 'td','',
status AS 'td',''
FROM #tlogtables
WHERE logUsed >= (@threshold) --AND databaseName in ('dbMaintenance','ReportServerTempDB')
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) +
N'</table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Default',
@recipients = 'abc@oracle.com',
@subject = 'Low Log Space Report',
@body = @body,
@body_format ='HTML',
@importance = 'High'
END
DROP TABLE #tlogtables
SET NOCOUNT OFF
August 9, 2024 at 10:59 am
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply