2

In a place where I work we have lots of subscriptions set for SSRS reports. Some reports have as many as 100 subscriptions set up.

Our data warehouse is refreshed every night, with all the data being dropped and loaded in again.

Because of that, on occasion when things go wrong and the data isn't loaded up in the morning when the subscriptions are supposed to run, they are unable to fill in the parameters and get bugged like this:

ssrs subscription parameter error

Now to fix them, we have to manually open every subscription and hit save again. Sometimes this is not enough and we have to manually set up every parameter again. This is very frustrating and time consuming. So this brings me to my question:

How to prevent this? Is there a way to automatically 'refresh' all subscriptions? Other solution I have thought of, but lack the knowledge of ReportServer db to execute is:

Perhaps run a script before the subscriptions are set to execute. The script would check if data exists in warehouse database tables. If there is no data, delay subscriptions for an hour and then try again. Is this doable? If it is, I would be extremely grateful for a query that gives me access to execution times of subscriptions. I tried and couldn't build one myself.

Any other solutions are extremely welcome, thanks in advance!

  • 1
    Can you store subscription parameters in another db? – McNets Jan 24 '18 at 9:29
  • Thanks for your response, that is one solution, however most of our parameters are 'dynamic', meaning we often have reports that will show data based on e.g. sales from the last week. That means that some parameters (like item category) will change depending on what was sold in the given week. That essentialy means we would have to refresh a db like that as well and it would be empty as well if things go south. The more I think of it I think that a script to check if there is data would be the best solution, Perhaps you know how to access subscription execution times in ReportServer db? – Mashchax Jan 24 '18 at 9:52
  • I'm sorry but I don't. – McNets Jan 24 '18 at 9:54
  • There is a Subscriptions table on RS db, maybe you can find some information there. And keep in mind that there is a job for every subscription. – McNets Jan 24 '18 at 10:00
2

What you do is to create your subscription to run only once. Then you go to the agent job it created and copy the step's code. This you can then run in a stored procedure after checking if there are data:

if (select * from table where date = '2018/05/18')
exec [ReportServer].dbo.AddEvent
    @EventType='TimedSubscription',
    @EventData='xxx data copied from job xxx'

The AddEvent will then execute the report to execute.

  • Does this automatically re-create the subscription with the new parameters or does it stub in the old values? – Shaulinator Nov 16 '18 at 17:15
  • Dang, I forgot about this question I asked. I like your approach, it looks like it could prevent the issue from happening at all - however, it would require going through each of the reports and setting this up the way you proposed. While I might actually do this if I ever find the time, the question was about 'refreshing' the subscriptions. I will post an answer later with a script that I came up with to do that. Whether it took me more time to figure it out than it would to implement your method, is a completely different debate :) – Mashchax Dec 7 '18 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.