Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Designer Sees Data Source but Preview Does Not


Designer Sees Data Source but Preview Does Not

Author
Message
sing4you
sing4you
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 2093
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?
higgim
higgim
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 2614
Sounds crazy but the name is def correct? Also, can you provide a screen shot of the error when trying to use preview?
sing4you
sing4you
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 2093
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
higgim
higgim
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 2614
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?
sing4you
sing4you
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 2093
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.
sing4you
sing4you
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 2093
Oh, and I also checked the parameter in the report - there is only one. It is the same data type as the receiving parameter.
higgim
higgim
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 2614
What so you mean by preview?
cphite
cphite
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 650
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.
sing4you
sing4you
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 2093
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search