July 9, 2012 at 11:44 am
I'm using SQL 2008 R2 Reporting Services enterprise edition. I have a report which needs to be sent out to 500 or so different email addresses. The report is different for each email address - basically the parameter for the report will be different for each email address, producting a different report.
Do I need to manually create 500 subscriptions or is there some shortcut? Is it even a good idea to have 500 subscriptions?
Thanks!
July 9, 2012 at 12:22 pm
I do the exact same thing with a single data driven subscription. It is a sales report that pulls each sales rep's results for the previous day and sends them an Excel spreadsheet outlining only their activity.
July 9, 2012 at 12:28 pm
Handling 500 subscription should not be a problem, just try not to schedule them all for the same time. You'll get SQL blocking along with the sudden stress of running all the queries involved all at once. Run 5 subs every 5 minutes or something.
As for a shortcut, it sounds like you should reconsider how your report was developed compared to how it is going to be used, or maybe SSRS isn't the tool you want, maybe SSIS instead.
July 9, 2012 at 12:34 pm
Can you tell me how you did it with a data driven subscription? I looked at that, but couldn't see how it could be done?
Thanks!
July 11, 2012 at 11:04 am
For a data driven query you'll need a source query/table/view that contains the email addresses and the parameters to supply to the subscription. The wizard pretty much walks you through the creation of the subscription. Where are you running into problems?
MWise
July 11, 2012 at 11:45 am
Steve-443559 (7/9/2012)
I'm using SQL 2008 R2 Reporting Services enterprise edition. I have a report which needs to be sent out to 500 or so different email addresses. The report is different for each email address - basically the parameter for the report will be different for each email address, producting a different report.Do I need to manually create 500 subscriptions or is there some shortcut? Is it even a good idea to have 500 subscriptions?
Thanks!
Since you have enterprise edition you should definitely take advantage of that. On the screen where you create a new subscription, you should see a button for creating a new data driven subscription; I believe you need to have content manager rights on the folder where the report resides. If you do have that access and still don't see the option, you either don't have enterprise edition or something is wrong.
The rest is fairly self-explanatory. You'll be asked to provide a query that will, in your case, return a list of the 500 email addresses. Over the next few screens, you'll tell SSRS to use that list as both the parameter of the report, and as an email destination for each time that it runs.
Once it's been setup, what happens is that SSRS runs the query and runs the report for each row it finds in the result. If it finds zero rows, it quits.
If, for whatever reason, you do NOT have enterprise edition or just cannot get the data driven thing to work, I would not recommend setting up 500 separate subscriptions. Firstly, it's going to be incredibly tedious not just setting them up but maintaining them. Second, if you try running them all at once you're going crush your server.
One workaround can be found here: http://www.tek-tips.com/faqs.cfm?fid=5918
Basically it's calling the ReportServer web service from VB.Net - I'm sure there are other ways. If you tweak the code to have it run a query that provides your parameters, you can have it loop through them in the same way that the data driven report would. I use this myself for a weekly job that calls the same report 1,500 times with different parameters.
July 11, 2012 at 12:25 pm
Thanks for the help! - I did figure out how to do it with the data-driven subscription. and yes, it was very straight forward - I was thinking too much!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply