NS 7 Automation Policy is sending a blank email to administrator every morning at 9am. The email did not contain a subject line or any helpful information.
Remote Access Purge Settings automation policy was setup to send emails at 9am. The policy is set to fire only on the condition of "Run for non-empty data". But it seems to fire even if nothing was purged.
Explanation of why it fires:
If you click on the
Edit query link in the Data Source section, and then select the
Resolved Query tab it will generate the SQL that is being submitted to the database server. If you copy and paste the resolved query results and run it against your database you will probably see (usually)
Total Records Left Records Deleted Records 0 0 0
This is why the policy is firing and sending an email. The query is actually returning one row. Albeit it isn't a row that we would want it to fire on.
Although this policy did not seem to be enabled, there is a 'Repeat' option that is checked. By unchecking the repeat option and saving the changes, we did get the blank emails to stop.
Another way of addressing the e-mails being sent out with 'no data' is to edit the query and use one of the included options for 'selecting' when you want the query to return data (even empty data);
declare @TotalRecords int, @LeftRecords int, @DeletedRecords int
set @TotalRecords = (select count(*) from PPA_AMT_MPSNotification) set @DeletedRecords = (select count(*) from PPA_AMT_MPSNotification where datediff(hour,CreatedDate,getdate()) > 24)
delete from PPA_AMT_MPSNotification where datediff(hour,CreatedDate,getdate()) > 24 set @LeftRecords = (select count(*) from PPA_AMT_MPSNotification)
SELECT @sQuery = 'SELECT ' + CAST(@TotalRecords AS nvarchar) + ' AS [' + @NTotRecords + '], ' + CAST(@LeftRecords AS nvarchar) + ' AS [' + @NLftRecords + '], ' + CAST(@DeletedRecords AS nvarchar) + ' AS [' + @NDelRecords + '] '
-- Return results only when all variables are greater than zero --if(0 not in (@TotalRecords, @DeletedRecords, @LeftRecords)) begin -- EXEC (@sQuery) --end -- Return results when one variable is greater than zero if((@TotalRecords >0 ) or (@DeletedRecords> 0) or (@LeftRecords > 0)) begin EXEC (@sQuery) end -- Return results only when records are deleted --if(@DeletedRecords> 0) begin -- EXEC (@sQuery) --end -- Run query regardless (causes automation e-mail even with empty/all zero data) --EXEC (@sQuery)
SMP 7 SP3, SP4
Imported Document ID: TECH122022
Subscribing will provide email updates when this Article is updated. Login is required.