SSRS report wont run with stored procedure

0
votes

I have an issue with 2 reports both for the same reason I am using SSRS/SQL 2014. My stored procedure takes in 2 dates and returns a very basic dataset, just 2 columns a name and a number. If I run the SP in SSMS it works perfectly fine but if I put it in to a report, the report just keeps running, doesn't fail but doesn't return any data. Originally I had a matrix in the report but to go right back to basics I just wanted to try and return the top one name from the data set but again the report just runs. I have checked to make sure that the account that is used to run the report has permissions which it does and it used on other reports but for this one nothing happens and I have run out of ideas where else to look. I have rebuilt the report a couple of times and I can see the SP fine and pull back the field names ok but no further success. I have built the report in Visual Studio and SSRS reporty builder. I appreciate this is a little vague but just looking for some further ideas what to try. Thanks

2
reporting-servicesssrs-2008
Can you post the stored procedure? – SS_DBA
First try to execute the query manually from the designer. In Visual Studio right-lick the dataset name, choose query and then click the ! button, add the parameter values when prompted and see what happens. If it works OK then it's likely the parameters are being passed/parsed incorrectly. If it does not work then run a trace on the SQL server box (SQL Profiler) and see what query is being executed on the server itself. Post the results of the tests here. – Alan Schofield

2 Answers

1
votes

Sorry for the delay in replying but only just got in to the office. Thanks for all the suggestions and I did go through them before sort of stumbling on the answer.

I did pass through the variables manually through the report and was getting nothing and when checking the profiler I could see that they were getting passed correctly.

I took the output and pasted it in to SSMS and was getting nothing but when I passed a smaller date range that originally I was getting information though there is data for both date ranges.

What I thought I would do as I sort of remember a similar problem many years ago, I actually marked the SP to be recompiled and ran it again in SSMS and then ran the original report in Visual Studio and on the reports server and now everything is working correctly. My only assumption is that the Execution plan has somehow got corrupted. I hope that this helps someone else.

0
votes

It may be that you have an open transaction in your stored procedure. This will not prevent the stored procedure from running in SSMS, but it would prevent the SSRS report from rendering. Your stored procedure should end with a semi-colon (;) at a minimum or possibly END/COMMIT/COMMIT TRAN depending on the start of your procedure.