Hi All,
I am in the middle of creating a new utility that will allow us to mass-add NSCAN messages for a list of donors from a list (rather than having to enter them one at a time), but I am having trouble setting up the report in BIDS. My procedure seems to work fine in SSMS. The end report is supposed to print out the selected parameters and the customer IDs and names in the list and whether the NSCAN message was added successfully or not (the NSCAN message is not added if the customer ID does not have the selected performance on their ticket history. This is to protect against adding a message to the wrong ID or also in the event the ID in the list is for an individual, rather than a household, as all ticket orders appear on our household records).
My problem arises when I go to set up the report in BIDS. I'm able to add the datasource, but when I add the dataset, the only field that shows up under that dataset is one called "ID", which is NOT a field in my report, and doesn't return any data when the report is run. I did some searching online, and this seems to be a common problem that other people have run into, but I haven't been able to find a solution. The only thing that seems to keep coming up involves adding "SET FMTONLY ON" to my code, but I still haven't been able to get fields I expect to see to appear.
I've been trying to figure this out for a few days now with no success, so ANY help anyone can provide would be greatly appreciated. Happy to work through this via phone or email.
Thanks for any suggestions anyone can provide!
David Rosen
Advancement Database Coordinator, Colorado Symphony
drosen@coloradosymphony.org
Anytime this has happened to me it was because I was not connected the data source. Are you sure you connecting to the data source? If you copy and existing report does it work as expected in the preview? If change the stored porcedure for the dataset on the copied report does you dataset fields load? If so, it is your data source connection.
Hi Travis,
I am definitely connected to the Data Source. From what I researched, I think it has more to do with IF statements that are inside of a Try Catch block, but I'm not sure how to fix it. Thanks for the suggestion though.
David
In that case why not move the select to the end and outside?
This sounds a lot like an issue with the stored procedure code. When SSDT does a "Refresh Fields" on your stored procedure, IT sets FMTONLY ON and calls the procedure in question (using all NULLs for the procedure parameters) to return the list of fields that the procedure outputs. You wouldn't set FMTONLY ON in your procedure code, but you COULD use it to test by replicating the issue in SSMS.
You can also verify this by running a SQL trace in SSMS while clicking the Refresh Fields button.
What I would do is look through your procedure code and make sure that you only ever select a result set to return in one place, and preferably do this in the top-level control flow of the procedure, instead of nested inside a bunch of IF statements. Stored Procedures don't provide any schema-level guarantees about what they will output, so SQL Server kind of has to make a best guess when doing a "describe first result set" operation. It's best not to make SQL Server work too hard here, or you can get unexpected results like what you're seeing.
—
Above: SSDT refers to SQL Server Data Tools, which is the new name for BIDS from SQL 2012 onward.
This problem has been resolved. Thanks for everyone's help!