When you’re working with subscribing to a report that contains parameters and the source table of the parameter has a nature of updating, deleting and inserting the values frequently, then there is a very good chance that you would have experienced this issue.
Here is a typical scenario:
You have a report that has a parameter named country. This values for this parameter is sourced from a SQL server table.
The values in the table are frequently modified or deleted apart from the new rows that are being inserted.
The table contains 3 values INDIA, CHINA, JAPAN.
You’ve a requirement to create an email subscription for the report with the parameter value “INDIA”. The subscription is set to run every hour.
You’ve created the subscription as per the requirement and everything works fine. Suddenly, after 5 hours you realize that the reports are not reaching your inbox as expected.
You check the subscription status and it has failed with the following message: “The subscription contains parameter values that are not valid.”.
When you check the reporting service log files, you see the following exception being logged there.
library!WindowsService_18!7b8!12/30/2013-11:00:15:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: , Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter ‘country' is not a valid value.;
You wonder why you’re getting this error. You quickly check back the source table and the country INDIA is no more there.
Here is what you need to understand. When you create a subscription for a specific parameter value or values, reporting service stores them internally and while executing the subscription, it will first validate whether the parameter value still exists in the source table.
If it is not there, the will be InvalidReportParameterException thrown. This sounds to be a genuine cause.
Now you went ahead and added the value INDIA back in the table. To your surprise, the email still is not reaching your inbox. When you check the subscription status in Report manager, it shows the last run as the time when it failed initially with InvalidReportParameterException.
You click on the EDIT option for the subscription and you see the parameter value INDIA still intact there. You save the subscription and exit from Report manager.
Suddenly, after an hour you start getting the email subscription as expected. Hasn’t it added more twist to your problem? If you’re still wondering why you’ve started to receive the email just after clicking on the EDIT and save, without actually modifying anything,
Here is the answer for this:
Reporting service maintains the subscription related information in its internal table named “subscriptions” within the Report server catalog database. This table has a column “InactiveFlags” which is by default set to “0”.
If you query the subscription table just after the occurrence of the InvalidReportParameterException and before adding the missing value back to the parameter source table, the “InactiveFlags” will show the value to be “8”.
After you add the missing value back to the source table, then EDIT the subscription and save it, the “InactiveFlags” value will be reset back to “0”.
Reporting service will process the subscriptions only if the “InactiveFlags” column contains the value “0”.
Now this will clearly explain the reason behind why adding the missing parameter back to the source table.
So, if you encounter this exception:
a) Make sure the missing parameter value is added back in source table.
b) EDIT and save the subscription.
This will ensure that everything keeps running until it encounters the InvalidReportParameterException exception again
If you’ve a lot of subscription with the parameter source table being changed too frequently you’ll be obvious to make this EDIT and SAVE of subscription automated. Here is the approach.
Create a SQL server agent job, and set the frequency of the job as per your requirement. The SQL command that needs to be executed is:
UPDATE [Subscriptions] SET InactiveFlags = 0 where InactiveFlags = 8
Now this will make sure the InactiveFlags column is updated periodically with a value of 0 if it contains the value of 8.
Based on my testing, I don’t see any issues with the above process event though:
Microsoft DO NOT RECOMMEND to change the values within Reporting Service catalog databases as it could destabilize the instance and make the instance to be un-supported.
Again, you could see the subscriptions failing with InvalidReportParameterException for genuine reasons. But as soon as the source table is updated with the missing value, your subscription will start the execution from the next scheduled run.
[All the posts are AS-IS with no warranty]