I'm working on a customization to the Performance Report, which will be scheduled to run once per day in the morning to be emailed to a list of recipients.
The way the canned report is designed, in the event that it is scheduled daily, it will run (and email) even if there are no perfs that day to be reported on. I want to try to suppress these empty emails.
Would the best way to accomplish this be to cause an error within the stored procedure in the event that no perfs are matched? Does anyone have a notion of what this code might look like? I'm not 100% sure about what errors would cause a complete halt in execution of the stored procedure. Am I correct in thinking this would prevent the email from being sent?
If I were you I'd write a sproc that would (via a scheduled SQL agent job) check to see if there were perfs that day, and if so, insert a one-time run of that report into the necessary tables, and if not, exit.
Oh interesting -- hadn't thought of that!
Eventually though, the distribution for this report will change based on the type of performance (and we have many, often multiple perfs per day), so rather than program all of that logic into a scheduled job, I think building the empty email suppression into the report will be more beneficial in the long run, and also easier for non-technical people to maintain.
If anyone comes across this in the future, here's what I ended up doing in my report procedure:
IF NOT EXISTS (SELECT 1 FROM #perfs) BEGIN RAISERROR('No matching performances today', 11, 2) RETURN END
#perfs is populated (or not) with any matching performances for the day when the report is run.
Unknown said: Oh interesting -- hadn't thought of that! Eventually though, the distribution for this report will change based on the type of performance (and we have many, often multiple perfs per day), so rather than program all of that logic into a scheduled job, I think building the empty email suppression into the report will be more beneficial in the long run, and also easier for non-technical people to maintain.
I wasn't thinking that any logic would be in the scheduled job, just the invocation of the custom sproc. So, the sproc would run every day, but only if it found the perfs it wanted (based on any desired logic) would a one-time job get inserted into the reporting tables. In any other case, the sproc would complete without doing so.
Maintenance by the end-users sounds good, too. I am imagining a custom system table, that one or more key users had access to, wherein they could set the desired "to" distribution address(es) by, e.g. perf type, perhaps. Then the custom sproc above could look for that info in the table before inserting the report jobs.