I'm looking for ideas as to why executing a certain stored procedure as a dataset does not work in a SSRS Report.
By does not work, I mean no data is returned.
In BIDS by going into the Shared Dataset Properties -> Query -> Query Designer -> ! (which runs the query). The column headers appear and data appear.
However, in the report itself, no data is returned. This stored procedure should return one row of data, no more no less. The dataset 'knows' the columns returned by the sproc in that the fields in the dataset properties are shown correctly. It has ` parameter, and I've tried hard-coding it, but makes no difference. When I look in SQL Server profiler on the database/server, indeed nothing happens when running the report.
I have another dataset in the same report, running another sproc and it runs correctly. This is sproc returns many rows. Is there something special about calling an sproc that returns only 1 row?