Designer Sees Data Source but Preview Does Not

  • 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?

  • Sounds crazy but the name is def correct? Also, can you provide a screen shot of the error when trying to use preview?

  • 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

  • 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?

  • 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.

  • Oh, and I also checked the parameter in the report - there is only one. It is the same data type as the receiving parameter.

  • What so you mean by preview?

  • 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.

  • 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