Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
anderskd
Starting Member
25 Posts |
 Posted - 2013-05-29 : 13:48:24
|
| Hello, I have been working on a project where we are deploying a couple SSRS reports across 45 different databases (SQL 2008 R2). The databases all have the same structure, so the same report can be used across the different dbs. I am setting up data-driven subscriptions for one report for each database, which runs once a month. I have a couple questions on what might be a best practice for something like this.First, I can't seem to find any useful way to setup the data driven subrscriptions using TSQL. Has anyone had any success with doing something like this? Even if we can't use scripts to setup the reports, it would be great if we could query the setup and schedules, to verify that they are all setup correctly without having to edit/view each subscription through report manager. Secondly, our reports are being delivered via email with the email addresses passed in as a parameter as part of the data-driven subscription. Each database could send between 50-1000 emails. We've had cases when a handful of the emails entered incorrectly, and these appear to get errored by the SMTP server. This doesn't provide very much useful information - For example (Done: 398 processed of 398 total; 8 errors.). The only place I can find information is on the SSRS log on the server, which still doesn't pinpoint anything to help identify which report specifically failed. Does anyone have any tips on a way to identify the reports that failed with more detailed information?Lastly, I'm guessing the users will change thier mind about some things, such as report name. If this happens, is there a way to keep/copy the data driven subscriptions to a new report or server?Thanks for the advice! Any other experiences or tips on managing large numbers of data driven subscriptions would be helpul. We don't have a huge amount now, but it looks like that number will be growing soon.Kelly |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-30 : 01:30:41
|
| by scripts do you mean setting the subscription properties itself programmatically insted of using report manager GUI?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
anderskd
Starting Member
25 Posts |
Posted - 2013-05-30 : 10:05:25
|
| Yes, ideally if there was a way to automate the creation of these subscriptions it would be easier when adjustments need to be made to each report X 40-50 databases.I believe I've seen some ideas using a .net app, but I was hoping for more of a general TSQL scripting method. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-31 : 02:27:52
|
| For that you've to use system stored proceduresCreateSubscription,CreateTimeBasedSubscriptionNotification,CreateTimeBasedSubscriptionSchedule etc present in ReportServerDBhave a look at definitions of above procedures------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
michael.malmgren
Starting Member
3 Posts |
Posted - 2013-05-31 : 11:11:00
|
| Hi Kelly, You might want to look into Boomerang Notification Framework from [url]http://fuel9.com[/url]. Its developer interface is entirely based on t-sql and it fully integrated with SSRS. In addition to outbound notification, like data driven emails subscriptions, it also supports a number of inbound notifications like email in. In short this will let you , with just a handful line of t-sql statements, to automate management of emails that bounce or error out.Cheers and good luck./M |
|
|
|
|
|
|
|
|
SSRS Data Driven Subscription Management