I have a report that may or may not have data. I have a subscription setup for this report. The subscription has a defined To list. I would like to NOT send the report if there is no data on the report. I did this in SQL 2000 by raising an error from the stored procedure if there was no data. However, that approach does not seem to work with 2005. Is there another way to accomplish this?

It shouldn't have worked before... The best way to do this is via data driven subscriptions. You can return no rows in the subscription query, which will result in no reports being sent.

Hi there, I really need to know if this can be done without data driven subscriptions. We only have standard edition and data driven subs are not available to us. How can I prevent the subscription emailing the report to the recipient when they have no data in their report?

AnnabelP wrote: How can I prevent the subscription emailing the report to the recipient when they have no data in their report? Hi, In SQL 2000 when there is no data, I can see this type of errors in the log when there is no data in the report, meaning that no report is being sent. In this example, we set-up subscriptions for users as Excel outputs. Failure sending mail: Exception of type Microsoft.ReportingServices.ReportRendering.ReportRenderingException was thrown. Now, how do you do it. Do you let users create subscriptions or do you create subscriptions for users? I believe Microsoft should do something about it. It is like the event driven sunscription idea where the report would be sent only when the data is there. See these posts for clues: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=20357&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=208887&SiteID=1 Regards, Philippe

How do we do it? 1. We create a subscription for each recipient. 2.Thereport has a parameter which is the unique to each recipient 3. The subscription is set up to contain the correct parameter for the recipient therefore we have many subscriptions one for each recipient/parameter. No error occurs and the email simply goes out with a PDF report attachment that has headings and footers but no data records. We just want the sub to execute if there is data. Our client want to advise the recipient of a report when they have 'unhappy' customers in the database. Most days the customers are happy but on days when a recipient has unhappy customers, we want to advise them. We don't want to send out an email stating that we have identified unhappy customers and for the recipient to open the PDF and find nobody in the report. They will only get confused. I will look at at the links you posted. Annabel

I never rpeorted to the forum about my solution using standard edition. here's what I did. 1. ran the subscription to file, writing the file to a network location. 2. Wrote a stored proc to gather the records in the table for which a report would have been created, and sent an email with the file attached. This meant that the SP controlled who received emails and who didn't. Only those where there was data had the email sent. The subscription still writes a file out for everyone but we only send the email if there is data. This whole subscription thing will only work for us whilst our client list is small. If it gets too big, we'll have to do data driven subs because I'm not sitting here creating a subscription for 100's of different people.

Has anyone considered the following: Create Schedule to render report Create Subscription to deliver report create shared assembly that contains a method which looks simular to TriggerSubscription(string SubscriptionID) this function will simply call FireEvent("TimedSubscription", SubscriptionID) Include assembly reference in report file and call TriggerSubscription method pending that a IIF Expression decides that the content's domain meets your delivery standards. In Production: Call Schedule. Report Renders from schedule's trigger If report domain requirements are met, IIF Expression will call assembly that triggers delivery subscription of report Report Delivered by subscription Does that make sense? (this way, there are relatively no dependencies aside what is actually on the report server) Todd Morrison

I can't seem to make this work in SSRS 2000.I have a static list of recipients, I can preview it in the IDE, but I get errors when trying to test it as "run once" subscriptions. So, essentially, I am trying to get yesterday's reports pushed out to the subscribers if there is data. Any tips you can provide would be appreciated. Brian J. Matuschakv-brmatu@microsoft.com

Did u get a solutioni to this problem? Do you still need some help?

Hi, Someone in this forum had an idea about this, sorry I could not recall the name. I applied the idea this way. I have a job with 2 steps, the first step is some kind of test to decide wether or not and when to fire the report subscription. If the first step fails, the report is not sent. In my case, I set the first step to try multiple times untill new data is there to be sent. Then, I have the second step set-up to execute the subscription like that: DECLARE @JobID BINARY(16) SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'D13B6027-627B-42EA-AB58-1BCA531D0355') IF (@JobID IS NULL) BEGIN -- send some sort of alert to the developer so he recreates the correct calls....Declare @SubjectTemp varchar(85)set @SubjectTemp = (select 'Daily Crawl Problem at ' + @@SERVERNAME )EXEC msdb.dbo.sp_send_dbmail @recipients = 'someone@somewhere.com; someonelse@mymail.net', @body = 'The report Daily Crawl D13B6027-627B-42EA-AB58-1BCA531D0355 could not run, it may have been updated on the report server, sorry.' , @subject = @SubjectTemp endelse beginexec msdb.dbo.sp_start_job @job_name = 'D13B6027-627B-42EA-AB58-1BCA531D0355'End It is not perfect but it works very well for me. The subscription has been created with an execution time in the past, i.e. 1:00 AMso it is easy to spot in the job list. Philippe Philippe

I have a simple solution Make your report based on a StoresProcedure, and use below example as template. CREATE PROCEDURE dbo.SP_CustTableList ( @Parm varchar(20) ) AS SET NOCOUNT ON SELECT * FROM CustTable WHERE Name = @Parm IF @@ROWCOUNT=0 RAISERROR ('No data to Subscription', 16, 1) RETURN GO Jan Borup Coyle

If you are not using Data Driven Subscriptions, Here's how I have achieved it http://www.sqlserverfaq.net/2012/01/05/how-to-prevent-firing-of-subscriptions-when-there-is-no-data-in-report/Parikshit

If you are not using Data Driven Subscriptions, Here's how I have achieved it http://www.sqlserverfaq.net/2012/01/05/how-to-prevent-firing-of-subscriptions-when-there-is-no-data-in-report/Parikshit