SSRS - How can I conditionally send a report to a mail address

Question

Let me specify the environment. The client has UI for entering orders in SQL Server database. A Windows Service one another machine is processing these orders from database at particular intervals. Sometimes the Windows service is stopped then orders piles up. To avoid that I have created a SQL Server Report which is run at an interval of 5mins. It checks how many orders are processed and creates a status report. What I want, if count of processed order is zero then the report be mailed to system administrator. Then he will check the machine where the service is hosted and restart the service, so that all rework is avoided. So the question is how to implement this conditional delivery of report. In short, if count is zero no report be mailed otherwise it must.

Answer 1

It's a common problem with SSRS 2005 (not sure about 2008) - there's no easy way to stop an empty report from being sent.

http://blogs.msdn.com/b/bimusings/archive/2005/07/29/445080.aspx

The solution I had to use (and yes, I know it's ugly, inefficient, and somewhat embarrassing...), is to run your search query in a stored procedure. If rows are not found, cause an error. If rows are found, re-run your query.

SELECT * FROM TABLE

IF @@RowCount = 0
BEGIN
    SELECT 1/0
ELSE
SELECT * FROM TABLE
END

Returning an error will prevent the subscription from emailing the report.