Ssrs dataset using a stored procedure

SSAS and SSRS
1

In an existing SSRS 2008 report, there is a dataset setup in a text format that looks like the following:
exec Sch..procSTEP2Reportxxryz @searchField, @searchFieldValue, @userCalendarId, @stepReviewDateFilter

This dataset is using a shared datasource called TBB. However the stored procedure actually exists another shared datasource called Sch.

What to do is to rename the existing stored proceudre from procSTEP2Reportxxryz to procSTEP2Report to meet my company's naming standards.

The problem that occurs is the SSRS report must not recognize the new stored procedure. It keeps showing me the parameter values that are listed above
and seems to want a blank or null value for each of the 4 parameters.

When I run I run the new stored procedure in SSIS manager and supply the 4 parameter values, it runs fine. However when I
exec Sch..procSTEP2Report @searchField, @searchFieldValue, @userCalendarId, @stepReviewDateFilter in the ssrs 2008 report, it thinks the parameter values are misssing.

In the dataset, I have tried to the following with no success:

  1. Change the shared datasource,
    2, Delete the parameters and readd them.

However nothing seems to work.

Thus can suggest what I should do to solve this problem?

2

Hello jassie1,

Sometimes the report's data is cached in a .data file that is saved in the same folder as the .rdl file. You can try closing the report, deleting the .data file and see if that forces the report viewer to refresh. I have never tried this for parameters, but it has helped me get rid of cached errors that keep popping up even after the problem has been resolved.

3

I've found that you may have to set defaults for the parameters. If that doesn't work, try initializing the dataset with a NULL, space, or blank for each param.

4

[quote="jassie1, post:1, topic:4825"]
parameter values are misssing[/quote]
There are some limitation and restrictions in renaming a stored procedure. Some of them are as:

1. SP names must comply with the rules which are commonly used for identifiers.
2. Renaming can not change the name of the corresponding object name in the sys.sql_modules catalog view. You should try to drop and re-create the stored procedure with its new name.
3. Changing the name or definition of a procedure can cause dependent objects to fail when the objects are not updated to reflect the changes that have been made to the procedure.

You should go this it may help yours https://blogs.msdn.microsoft.com/axsupport/2012/11/20/parameter-is-missing-a-value-error-running-a-customized-report-in-microsoft-dynamics-ax-2012/

5

[quote="jason_clark, post:4, topic:4825"]
Renaming can not change the name of the corresponding object name in the sys.sql_modules catalog view. You should try to drop and re-create the stored procedure with its new name.3. Changing the name or definition of a procedure can cause dependent objects to fail when the objects are not updated to reflect the changes that have been made to the procedure.
[/qu

6

I did the following, "Renaming can not change the name of the corresponding object name in the sys.sql_modules catalog view. You should try to drop and re-create the stored procedure with its new name.".
Can you tell me what I need to do with " Changing the name or definition of a procedure can cause dependent objects to fail when the objects are not updated to reflect the changes that have been made to the procedure."? How can I change the dependency between of the dependent objects?