June 21, 2017 at 1:03 am
Hi
We have 470 subscriptions in total, of which 224 are for the same parameterised ssrs report but pass different parameter values (including some parameters having multiple values selected, eg: location). Unfortunately we lost all 224 subscriptions when the report was removed then re-added.
How can the subscriptions be re-created quickly & easily please without hours of data entry?
The SSRS reports were created using Report Builder 3.0 using a Data Source to an SQL Server 2012 database, parameters (including multi-select parameters), shared data sets and a dataset to a stored procedure. They are accessed via a Report Server.
P.S. These are NOT Data-Driven Subscriptions, but subscriptions created manually 1 by 1 for particular user-defined reports with user-defined parameters & shared data sets with a common data source, all stored in the report file (eg: myreport.rdl)
June 21, 2017 at 10:58 am
shell_l_d - Wednesday, June 21, 2017 1:03 AMHi
We have 470 subscriptions in total, of which 224 are for the same parameterised ssrs report but pass different parameter values (including some parameters having multiple values selected, eg: location). Unfortunately we lost all 224 subscriptions when the report was removed then re-added.
How can the subscriptions be re-created quickly & easily please without hours of data entry?
The SSRS reports were created using Report Builder 3.0 using a Data Source to an SQL Server 2012 database, parameters (including multi-select parameters), shared data sets and a dataset to a stored procedure. They are accessed via a Report Server.
Do you have a backup of the ReportServer database from when it still had the subscriptions? If you have that, you can probably use powershell or rs.exe to get the subscriptions. Without that, I don't think it's doable since there would be no definition of the subscriptions anywhere else.
Sue
June 22, 2017 at 10:40 pm
No I don't believe so. Although there should be an easier/quicker way to creating many subscriptions for the one parameterised report.
June 23, 2017 at 11:40 am
shell_l_d - Thursday, June 22, 2017 10:40 PMNo I don't believe so. Although there should be an easier/quicker way to creating many subscriptions for the one parameterised report.
The definition for the subscriptions are stored in the database. Where else did you store the parameters used for each of the reports and the destination for each report (e.g. email address)? Since you have them somewhere else, you can use Powershell to rebuild the subscriptions.
If you didn't store them somewhere else and don't have backups where exactly is the information suppose to come from?
Reporting Services databases should be backed up just like other databases.
Sue
June 27, 2017 at 1:04 am
Sue - They are not data-driven subscriptions but subscriptions for a user-defined report with user-defined report parameters, which are all stored in the report file itself (myreport.rdl), not the database.
June 27, 2017 at 6:24 am
shell_l_d - Tuesday, June 27, 2017 1:04 AMSue - They are not data-driven subscriptions but subscriptions for a user-defined report with user-defined parameters, which are all stored in the report file itself (myreport.rdl), not the database.
Wrong. When a subscription is defined, it is not stored in the report itself - myreport.rdl does not change each time someone subscribes to a report. The subscription definition is stored in the subscription table - along with the parameters defined, what the target is (email, file share, etc). I described standard subscriptions and never thought they were data driven.
When a person subscribes to myreport.rdl and then you delete myreport.rdl, you just deleted the subscription. Not understanding that is part of the problem.It is not stored with the report.
Even if a subscription was (and it's not) stored with the report, you deleted the report so the subscriptions would have been lost in such a scenario. And the report would have been in a backup of the ReportServer database. So it wouldn't even matter as there are no backups.
The reporting services databases need to be backed not just for server issues, corruption, lost hardware, etc but also for people doing things like this. And that is the second part of the problem.
So it's not an issue with SSRS. It's a problem with not understanding how reports and subscriptions work as well as not having backups.
Sue
June 27, 2017 at 8:27 am
I agree with Sue on this one (which is usually the case); report subscriptions are stored in the database not the report. Storing them in the report would require having the report server modify the rdl on the fly which sounds like a risky thing to do.
If you can't powershell it back, you could speed up the subscriptions process by pushing it out to the end users and have them create their own subscriptions.
Instead of you manually making 400+ subscriptions to 200+ reports, have the end users re-create the subscriptions for the reports they care about. You may find that you no longer need 400+ subscriptions.
I know where I work, we have a lot of people subscribed to reports who immediately delete the report emails as they no longer require them instead of getting us (or them) to change the subscription.
You should have backups of the ReportServer database as well as the system databases as SQL Agent jobs are created for sending out the subscriptions (kind of... technically the job writes to a table to tell the report server to fire off the subscription).
In the event that you were correct about the subscription being stored in the rdl file, re-creating the subscription should be as simple as just re-uploading the rdl.
If you have a test system, does the test system still have the subscription but going to a data-dump email address? If so, you could likely pull the subscription data out of the test system and put it into the live one.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 5, 2017 at 9:08 pm
Sue, you misunderstood me, I did not mean that the subscriptions are stored in the report, I meant that I believe the following are in the user-defined report:
* user-defined report parameters
* datasource
* datasets (including shared)
What if I need to create 400 brand new subscriptions (not data driven subscriptions) for a report & they are all definitely required, how does one do this without spending hours upon hours entering them????
I completely understand that the subscriptions are deleted when the report is, that is not being questioned here.
July 6, 2017 at 7:27 am
shell_l_d - Wednesday, July 5, 2017 9:08 PMSue, you misunderstood me, I did not mean that the subscriptions are stored in the report, I meant that I believe the following are in the user-defined report:
* user-defined report parameters
* datasource
* datasets (including shared)What if I need to create 400 brand new subscriptions (not data driven subscriptions) for a report & they are all definitely required, how does one do this without spending hours upon hours entering them????
I completely understand that the subscriptions are deleted when the report is, that is not being questioned here.
Nope. The parameters a user defines for a subscription are stored in the subscription table, not in the report.
The delivery extension is stored in the subscription table.
The specifications for the delivery (share or email, render format, etc) are stored in the subscription table.
The schedule for the subscription is stored in the subscription table.
The subscription definitions are stored in the subscription table. If you delete a report, the report and subscriptions are deleted.
If data is deleted from a table and you do not have backups, you lose the data that was stored in the table.
If you do not have the data anywhere else, it is gone.
If you are creating brand new subscriptions, you need to have all of the above information.
If you have that data for each subscription you are creating, put it in some format you can logically read from so that the values for each individual subscription can be obtained, write a program or script to loop through all of the values for each subscription and create the subscriptions using Powershell or the Reporting Services Web Service.
Sue
July 6, 2017 at 9:05 am
Sue_H - Thursday, July 6, 2017 7:27 AMshell_l_d - Wednesday, July 5, 2017 9:08 PMSue, you misunderstood me, I did not mean that the subscriptions are stored in the report, I meant that I believe the following are in the user-defined report:
* user-defined report parameters
* datasource
* datasets (including shared)What if I need to create 400 brand new subscriptions (not data driven subscriptions) for a report & they are all definitely required, how does one do this without spending hours upon hours entering them????
I completely understand that the subscriptions are deleted when the report is, that is not being questioned here.
Nope. The parameters a user defines for a subscription are stored in the subscription table, not in the report.
The delivery extension is stored in the subscription table.
The specifications for the delivery (share or email, render format, etc) are stored in the subscription table.
The schedule for the subscription is stored in the subscription table.The subscription definitions are stored in the subscription table. If you delete a report, the report and subscriptions are deleted.
If data is deleted from a table and you do not have backups, you lose the data that was stored in the table.
If you do not have the data anywhere else, it is gone.If you are creating brand new subscriptions, you need to have all of the above information.
If you have that data for each subscription you are creating, put it in some format you can logically read from so that the values for each individual subscription can be obtained, write a program or script to loop through all of the values for each subscription and create the subscriptions using Powershell or the Reporting Services Web Service.Sue
I agree with Sue on this.
In the event you don't have the data in any useful format or can't get it in a useful format and thus can't script it; push this back on the end users. End users can make their own subscriptions to reports. Get them to subscribe to the reports they care about (where possible).
Having 1 person create 400+ reports will take hours if not days. Having 400+ people create 1 report each will take minutes.
I would also strongly encourage you to take backups of the server. re-creating 400+ subscriptions due to an accidentally deleted report when you have backups should not take very long to get it back... Much less time than restoring 400+ reports by hand.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 7, 2017 at 1:34 pm
Any update on this?
I am curious if you found a different solution to the problem that we didn't think of or if you just manually re-created all of the report subscriptions by hand?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 11, 2017 at 9:35 pm
Sue, sorry but you have misunderstood me again. You are referring to SUBSCRIPTION parameters, I am referring to REPORT parameters which are stored in the report, as I can see them in the xml code when I open the *.rdl report file in notepad.
As previously mentioned, I am not disputing you about the fact that the subscriptions delete when you delete the report.
As per my original question, how do I create many subscriptions without manually typing every one of them in? I don't have Powershell. We have a Report Server & Report Builder 3.0. I haven't received a solution to this original question.
bmg002 - I cant push it back to the end users, it is my job to do it. Sorry, but I don't consider this a solution.
Anyone Else have a solution to my question please?
July 11, 2017 at 10:49 pm
You don't have PowerShell? Why not download it?
July 12, 2017 at 5:56 am
shell_l_d - Tuesday, July 11, 2017 9:35 PMSue, sorry but you have misunderstood me again. You are referring to SUBSCRIPTION parameters, I am referring to REPORT parameters which are stored in the report, as I can see them in the xml code when I open the *.rdl report file in notepad.
As previously mentioned, I am not disputing you about the fact that the subscriptions delete when you delete the report.
As per my original question, how do I create many subscriptions without manually typing every one of them in? I don't have Powershell. We have a Report Server & Report Builder 3.0. I haven't received a solution to this original question.
bmg002 - I cant push it back to the end users, it is my job to do it. Sorry, but I don't consider this a solution.
Anyone Else have a solution to my question please?
Actually no, never misunderstood anything you have said. It may be more likely that you aren't understanding what I am saying. I am referring to regular subscriptions, not data driven. I have always been referring to regular subscriptions. Already said that.
You've been given solutions - there is no button to push that will generate all the subscriptions that you deleted. It's a bit dangerous to still be running XP or earlier versions of Windows - Powershell is installed by default on all of the subsequent operating systems. As already suggested, you can download it. This version will work on XP SP3. If you are actually on Windows 7 or later, you already have Powershell:
Windows Management Framework (Windows PowerShell 2.0, WinRM 2.0, and BITS 4.0)
And you already have rs.exe. So you have been given two solutions. You use Powershell or the web service. And of course you know rs.exe is designed for exactly that. If you don't want to download anything, then just use rs.exe. It comes with Reporting Services, not just data driven subscriptions either. You can copy the file to any other computer. The default location is Program Files\Microsoft SQL Server\<YourVersion>Tools\Binn
So you've got all you need to do this. As long as you understand it, there really isn't anything else. And that would hold true for any type of subscription.
Sue
July 12, 2017 at 8:31 am
Adding to what has been said, I am slightly confused as to why you would manage the subscriptions for end users.
I understand if the subscriptions are emails being sent externally, but it seems very inefficient to have 1 person manage all of the subscriptions on a reporting server. For example, lets say that Steve wants to subscribe to "Super Aweseome Report" on a daily basis. 6 months down the road, he realizes he only looks at that email once per week so he wants to get it changed to weekly. Would Steve need to contact you to get the original subscription set up and then contact you again 6 months down the road to get it changed to weekly?
To me, this feels like a waste of the DBA's time. End users were given the ability to subscribe to their own reports for the above scenario and others. What if Steve no longer needed that report subscription?
I can understand the DBA handling it if a user is let go for example as they won't be unsubscribing from things if they cannot access the system any more.
Now, I do understand that this may be company policy that the DBA handles the SSRS subscriptions, but it might be worth making a business case to push that back to the end users where possible.
And as for the REPORT parameters vs SUBSCRIPTION parameters, I am pretty sure you are misunderstanding that. The REPORT parameters are stored inside the report. That part is correct. But there is nothing to say that a report parameter is unique to a subscription. You can have 100 subscriptions all using the same report parameter and going to different emails and files and SSRS will not complain. The report parameters are not associated with the subscription parameters, except in the sense that the subscription parameter must be a report parameter. But presuming your report parameters are what drive your subscription parameters, you should be able to build up either a powershell solution or a rs.exe solution.
Adding to Sue's thing on powershell, does your server hosting SSRS have powershell? If you cannot install powershell on your desktop (as it is XP or older), I imagine your server has powershell on it. If memory serves, SQL Server 2012 requires Windows Server 2008 or higher and I am fairly confident that it comes with powershell. I know it isn't recommended to do things on a SQL server directly, but if the options are rebuild the subscriptions from scratch or log into the server and use powershell, I'd jump into the server. Unless you are not running this on a Windows Server system, but on a desktop system in which case I think you need to do a lot more work than just get the subscriptions back up.
But as long as you have some way to manually re-create the subscriptions, you should be able to do something semi-automated. But knowing who the subscriptions should go to for each parameter; that is something we don't know so it makes it hard to determine how you are solving that. Are the report parameters the email addresses it is supposed to send things to?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply