Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Designer Sees Data Source but Preview Does Not Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 1:51 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 499, Visits: 1,994
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?
Post #1543210
Posted Thursday, February 20, 2014 1:53 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:32 AM
Points: 405, Visits: 1,310
Sounds crazy but the name is def correct? Also, can you provide a screen shot of the error when trying to use preview?
Post #1543355
Posted Thursday, February 20, 2014 9:46 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 499, Visits: 1,994
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


Post #1543597
Posted Thursday, February 20, 2014 12:32 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:32 AM
Points: 405, Visits: 1,310
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?
Post #1543671
Posted Thursday, February 20, 2014 3:10 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 499, Visits: 1,994
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.

Post #1543747
Posted Thursday, February 20, 2014 3:19 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 499, Visits: 1,994
Oh, and I also checked the parameter in the report - there is only one. It is the same data type as the receiving parameter.
Post #1543750
Posted Thursday, February 20, 2014 3:31 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 2:32 AM
Points: 405, Visits: 1,310
What so you mean by preview?
Post #1543754
Posted Thursday, February 20, 2014 3:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:40 PM
Points: 70, Visits: 387
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.



Post #1543758
Posted Thursday, February 20, 2014 3:55 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:54 AM
Points: 499, Visits: 1,994
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.
Post #1543763
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse