Hi. I am currently workingWith SSRS . I have created a Store Procedure To show Data from different table in my Database,I have Used a temp table with #xxx Name,when i am creating DataSet for this Store procedure . but its not Showing the Fields of Store Procedure, but when i create other Store procedure without temp table with #xxx then its showing the fields in Data Set. What Could be the possible Reason, Thanks

Hi, This is due to lack of metadata for results from stored procedures in general. Typically the shape of the result-set is determined by doing SET FMTONLY ON. The FMTONLY setting will cause temporary table creation etc to do nothing, so subsequent statements referencing temporary table or created objects will fail. So you cannot determine the result-set without actually executing the stored procedures under such circumstances. This is also true if you return different result-sets depending on parameters etc. However, the SET FMTONLY ON only happens in the Report Designer when leaving the Data View to go to the Layout View. This is used to determine the Fields list, i.e. to populate the Datasets tree view. For the case of stored procedures with temporary tables, we provide a specific alternate method of determining the fields list which avoids the SET FMTONLY ON. The alternate method is when in Data View and using the Generic Query Designer, you need to click on the Refresh Fields button. This will then prompt you for parameter values and determine the fields list (schema) by running the query. Note that this is only an issue in the Report Designer where the schema must be determined. There is no problem with using stored procedures with temp tables when running a report on the server. Hopefully this helps. Thanks.

Hi Can you please make sure that whichever temp table you are using in your stored procedure ,are you selecting the columns from that,because only after selecting the columns from the temp table you will be getting those fields in your dataset. For which you are getting the fields without the temp table. hope it will help you. Anyway please let me know the solutions you got. Thanks Mahasweta

I am using 2008 R2 and it is an issue still and even if report is deployed to the server - it still does not work