February 19, 2014 at 1:51 pm
I am developing a new report in SSRS 2008. The designer sees the stored procedure and adds the fields successfully. However, when I try to run the report, it says it cannot find the stored procedure. I have refreshed the stored procedures in the database. I don't know what else to try.
Ideas?
February 20, 2014 at 1:53 am
Sounds crazy but the name is def correct? Also, can you provide a screen shot of the error when trying to use preview?
February 20, 2014 at 9:46 am
Yes, it is the correct SP name. It is getting it from the designer. The designer got it from a pick list that it created because it sees the database. I also checked the name against the database to be sure. I made sure that the name in the SP matched the name in the object list.
I can't get the image insert function to work so here is the message I am receiving:
An error occurred during local report processing.
An error occurred during report processing.
Query execution failed for dataset 'Hotel Costs'.
Could not find stored procedure 'Rpt_HotelsCostForFiscalYear_Get'.
I am dead in the water here.
Thanks,
Liz
February 20, 2014 at 12:32 pm
That error rings a bell.
Try
Run the SP in SSMS. Any errors?
Strip the SP and run it outside of the SP.
Do all returned columns from SP have a proper name?
Are all parameters set correctly and match what is expected?
February 20, 2014 at 3:10 pm
I always start an SP as a query. Once I have the query and the variables working, I turn it into a stored procedure. The query runs fine in SSMS and the stored procedure works fine in SSMS. The columns have proper names and SSRS in design mode sees them and lists them. It is just when I try to run the Preview that it can't find the SP.
February 20, 2014 at 3:19 pm
Oh, and I also checked the parameter in the report - there is only one. It is the same data type as the receiving parameter.
February 20, 2014 at 3:31 pm
What so you mean by preview?
February 20, 2014 at 3:45 pm
sing4you (2/20/2014)
Yes, it is the correct SP name. It is getting it from the designer. The designer got it from a pick list that it created because it sees the database. I also checked the name against the database to be sure. I made sure that the name in the SP matched the name in the object list.I can't get the image insert function to work so here is the message I am receiving:
An error occurred during local report processing.
An error occurred during report processing.
Query execution failed for dataset 'Hotel Costs'.
Could not find stored procedure 'Rpt_HotelsCostForFiscalYear_Get'.
I am dead in the water here.
Thanks,
Liz
Go into your dataset and make sure that you can actually execute the stored procedure - either by hitting Refresh Columns, or by going into Query Designer and running it directly. Oftentimes if there is an error, it gets passed as "cannot find..." in the preview mode.
Bear in mind that some errors are specific to SSRS - for example, SSRS will throw an error if you have the same column name twice, even though in SSMS it will run successfully.
Also, even though it shouldn't be necessary, try switching your Query Type to Text, and then using the fully qualified name; in other words:
exec yourDatabaseName.dbo.[Rpt_HotelsCostForFiscalYear_Get] @yourParameter
If you can refresh fields with that, and the report still doesn't work, then it's possible something is corrupted in the .rdl file itself. In that case, your best bet is to completely delete the dataset and try creating it again.
February 20, 2014 at 3:55 pm
Thank you all for your help. We finally fixed it. There was a file deep down in the bin that somehow had gotten set to read-only. This was causing all the trouble.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply