Hi, I am not specifying database name in my dynamic SP parameters. Below is my SP code... ALTER PROCEDURE dbo.MyProcedure @Employee varchar(40) AS BEGIN DECLARE @sql varchar(max) SET @sql=' ' IF @Employee = '<ALL>' SET @Employee = 'MyList' SET @sql='SELECT * FROM Table WHERE Emp = ''' +@Employee + '' SET @sql='SELECT * FROM OPENQERY(MySQLLinkServer,'''''+@sql+''''')' EXEC (@sql) END I don't want to add fields manually into dataset as it could be big columns list or in future it could change. Any idea why dynamic SQL store procedure not showing data set fields in Visual Studio 2008? Thanks. ??

Hi, I have Visual Studio 2008 (9.0.30729.4462) and SQL Server 2008 R2 store procedure. I added a report to Report Server project and created data set to execute SQL store procedure. I can execute store procedure (Query Designer button) successfully by passing parameter values and I clicked on "Refresh Fields" but fields is not showing. I found below links which shows its bug but it posted on April-2008 http://connect.microsoft.com/SQLServer/feedback/details/337715/katmai-2008-reporting-services-store-procedure-dataset-not-showing-fields Any idea why store procedure not showing data set fields for Visual Studio 2008? Thanks.

Are you using dynamic sql within a stored procedure?Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Yes, I am using dynamics sql inside my store procedure.

Hi, Are you specifying the database names used in the SP dynamically as a parameter ? B'cause I also came across same scenario where SP was executing successfully upon specifying parameters, but no fields were present in the concerned dataset. Later I defined the fields name manually (as in SP) and verified using Table control. It executed successfully. Please explain your scenario... Regards,Avinash.Thobbi

Hi, I am not specifying database name in my dynamic SP. Below is my sp code. ALTER PROCEDURE dbo.MyProcedure @Employee varchar(40) AS BEGIN DECLARE @sql varchar(max) SET @sql=' ' IF @Employee = '<ALL>' SET @Employee = 'MyList' SET @sql='SELECT * FROM Table WHERE Emp = ''' +@Employee + '' SET @sql='SELECT * FROM OPENQERY(MySQLLinkServer,'''''+@sql+''''')' EXEC (@sql) END I don't want to add fields manually as it could be big columns list or in future it can change. Any idea why dynamic SQL store procedure not showing data set fields in Visual Studio 2008? Thanks. ??

Hi, From your description, it is unusual that you can execute the stored procedure in Query Designer but the data fields cannot be retrieved when click the Refresh Fields button. I also did a test with a similar stored procedure and it works fine. Please make sure to click the OK button after clicked the Refresh Fields button in the Dataset Properties window, and then check the dataset in the Report Data pane again. Currently, in order to retrieve the information of data fields, one workaround is to change the query type to "Text", and then use the following script: SET FMTONLY ON; EXEC <Stored Procedure> SET FMTONLY OFF; Since the query will only return the data fields names, after executed it, please change the query back to the original procedure. For more information about FMTONLY, you can refer to: http://msdn.microsoft.com/en-us/library/ms173839.aspx However, if it still doesn’t work, please let me know and also post the Report Designer version and SQL Server version. Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi, If You Dont have many Columns from your Stored Procedure What you can do 1.Go to your Dataset and Add a New Calculated Field. 2.Set your Field Name and your Field Source as Your Column Name. 3.Save and Run it in your Reports. Mark it as Answer if you Find this Reply Helpful.Rakesh M J

Then you need to add those fields to the dataset manuallyBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/