December 30, 2014 at 1:26 pm
Hi,
Am facing a issue with scheduling a report through data driven approach. The report accepts 6 params out of which one is static int, 2 are date time and 3 are text.
RDL earlier had a param of type text and was removed recently. Hence the subscr proc was also modified to not to return additional column. It was working earlier and after the removal of the param, it is throwing this error consistently.
December 30, 2014 at 1:39 pm
Check how it executes the stored procedure. If that parameter is still part of that process, it will need to be changed to not use the parameter.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 30, 2014 at 1:48 pm
Thanks for the reply. I traced it to confirm the params used for the proc. Proc doesn't include the removed param while executing!
Also the report manager shows it would accept only 6 params in the subscription creation page.
December 30, 2014 at 2:28 pm
I meant how the report executes the stored procedure. If it uses query text to do an EXEC sp_name @parm1, @parm2, ... then you might need to make a change to that query text. That's not the default methodology for SSRS, but it's something to look at. In other words, you want to look at the part of the report that identifies what stored procedure to execute, and any parameters that are part of that setup, and being sure that this parameter is removed from that part of the report.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 30, 2014 at 4:57 pm
I actually configure the subscription proc to be called by RS for retrieving parameters that will be used in main report execution.
When i said parameter was removed, it was removed from RDL. The subscription proc doesn't have any related parameter for this i.e subc proc returns list of parameter values for the parameters (RDL param's) defined in RDL. The one that was removed was RDL parameter and i ensure proc doesn't return additional value for the removed RDL parameter.
I verified the main report doesn't use the removed param.
I think RS has not gone that far to execute the actual report or it is throwing when trying to execute the actual report!
Not sure if this is clear!
December 30, 2014 at 10:03 pm
Unfortunately, that's about as clear as mud. When you set up a report to execute a stored procedure, there are a couple of ways to do it. You can specify the query as EXEC sp_name @parm1, @parm2, etc... OR ... you can go through what amounts to a wizard by telling SSRS you have a stored procedure to work with, and it automatically figures out what parameters the sproc needs, and then you can supply values or configure SSRS parameters for each parameter the sproc needs. What I meant was that if you had happened to choose the former method over the latter, you might need to make more extensive changes, as even the query would have to change. In the case of the latter, you would need to be sure that the parameter that's been removed is not referenced ANYWHERE in the RDL, so it might be worth a quick look at the actual RDL file using NOTEPAD or NOTEPAD++. There are a LOT of places a parameter might appear as part of an expression. Any such appearance could cause an error. Of course, even the former method would require that one eliminate all traces of the parameter in the RDL. Hopefully, I've at least made it clear what I was talking about. If not, let me know.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 30, 2014 at 11:08 pm
Yes that's the first thing i checked when started debugging this.... Am using the latter approach...
There is no reference to the old parameter in RDL.
December 31, 2014 at 6:26 am
Okay, then perhaps its the setup of the subscription itself, as I suspect that you may have that field that was removed in use within it, and if you haven't already, I'd try setting up a new subscription, and when it works, delete the old one. This will force you to set up any fields that were in use, and give you the opportunity to copy from the existing one. Ultimately, it may at least lead to eliminating another possibility.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 31, 2014 at 9:41 am
I have recreated subscriptions a dozen times 🙁
December 31, 2014 at 9:51 am
Please note that you have yet to say exactly what error you are receiving and at what point in time. Any ideas I've had so far have been based solely on general troubleshooting principles, and without detailed information, there's really no further I can go. You're going to need to disclose the exact error, along with the text of the stored procedures (both for the report AND for the subscription), and a screen shot(s?) of the subscription setup process you're going through.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply