I'm working with Report Builder 2.0 (we don't plan on getting SQL 2008 R2 - so NO 3.0 for us!) and have found that if I have an existing report using an existing stored procedure (it has 4 existing parameters) and I add a new field to the stored procedure it will not refresh the data set. I have gone to the DataSet properties and executed the query(SPROC) where it does show the new field (so I know the sproc will execute), but when I click OK and hit Refresh Fields it stops responding. I also tried the same steps without the Refresh Fields and still stops responding. I also tried to add the field name and it errored out as invalid.
The only work around I've found is to create a new dataset referencing the updated SPROC with a different name, deleting the old dataset and then re-naming the newer dataset with the old one's name so all the field references are the same. I do know that in the case of renaming a field in a sproc that this would end BADLY because there is no way any of the old field's references would work.
But it seems odd I have to go through so much to refresh for new fields, so perhaps I'm missing a step in dealing with this situation. Any recommendations would be appreciated.
I understand that the Report Builder 2.0 hangs while flush the dataset by executing the query. In that case, I think it is necessary to capture a user dump when the problem occurred. And then, you can send the dump to Microsoft CSS support. Support Professionals from Microsoft will assist you to solve the problem.
I am providing the steps below for you to gather the hang dump or crash dump to further work on this issue,
a. Open Report Builder as usual, and wait for a while.
b. When you found that the Report Builder application hang, and was not responding, please open TaskManager by one of follws,
- Pressing “Ctrl + Alt + Del” button and then click “Start Task Manager” item.
- Or right-clicking your task bar, click “Start Task Manager”menu.
c. Once the Task Manager opened, switch to Process tab, find the “Report Builder” process MSReportBuilder.exe.
d. Right-click the item, click Create dump file.
e. After that, it will prompt a window and let you know the location of the dump file.
You can copy the hang dump or crash dump file to your development machine configured with symbols files, and open it in Visual Studio 2008. Next, you can analyze the error / exception, and call stacks to see why the error happens.
Thanks Amey. You end up saving me a couple of steps from my original posting. It's still not a 'fix' to the hanging upon refreshing an altered sproc, but it works. I wish I had the time to figure out for the Microsoft CSS team why it's hanging, but I don't think ReportBuilder 2.0 is as important now that 3.0 is out there.
I had the same thing, using a shared dataset, and my newly-added fields, which did show up in the actual dataset but not in the shared one inside the report. Not even Refresh Fields from within the shared dataset inside the report would do it.. I actually had to change it to a different one, refresh fields, then back to the right one, refresh fields.. and then it worked..
.. i.e. the field showed, and I could pick it for the report, but when the report actually ran there was no data showing.. brutal!
fix is to just delete the shared dataset and add a new one [using the same name so you don't have to go through your report objects] and then the added fields show, and show data in the report..