Arthur's solution will surely work, but be aware that using a stored procedure isn't always the best option. Stored procedures don't have an explicit metadata contract, so in order to retrieve the fields metadata SSRS has to execute the sp (this is a SSIS forum by the way). It's better to use a table valued function, as those do have a metadata contract. Read more about it here: http://sqlblog.com/blogs/rob_farley/archive/2011/11/22/stored-procedures-with-ssrs-hmm-not-so-much.aspxMCTS, MCITP - Please mark posts as answered where appropriate.

Hi all, I am using MS SQL Server 2008 R2 32-bit Standard Edition, which is installed on Windows Server 2008 R2 64-bit Standard Edition. Reporting Services Native Mode is installed on the same Windows Server operating system, but lives in a 64-bit MS SQL Server 2008 R2 Standard Edition instance. From my Windows 7 64-bit machine where the SQL Server tools are installed, I am using BIDS to create reports. One of my reports has a date parameter whose defalut value is NULL. It currentl has five fields that are retrieved from a stored procedure. Th data source is a shared datasource. The dataset propety "Time out" is set to zero (0). The problem I am facing is the following: I added one more field into my stored proceedure. So I need to refresh the dataset from within BIDS so that this new field may show up. However, I keep on receiving the following error message: "Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." I know for sure that my connection is working because I am able to connect to the server using other applications. Also I know my stored procedure is working fine because when I execute it directly from within SQL Server Management Studio, it runs smoothly. Therefore, what would be the fix for such an annoying issue? What is the solution? I thank you in advance for your time.

Delete and re-create this data source and make sure it retains the same name.Arthur My Blog

Arthur's solution will surely work, but be aware that using a stored procedure isn't always the best option. Stored procedures don't have an explicit metadata contract, so in order to retrieve the fields metadata SSRS has to execute the sp (this is a SSIS forum by the way). It's better to use a table valued function, as those do have a metadata contract. Read more about it here: http://sqlblog.com/blogs/rob_farley/archive/2011/11/22/stored-procedures-with-ssrs-hmm-not-so-much.aspxMCTS, MCITP - Please mark posts as answered where appropriate.

Hi ArthurZ, Thank you for taking the time to address my inquiry. However, I followed your steps by deleting the dataset and the data source, and the problem was not solved. I keep on receiving the timeout expired message. Do you have any more idea about this issue?

Hi Amir, Can you create a new package instead leaving all the options at their defaults? And if the SP runs fast in SSMS then SSIS must proceed as quick, therefore the issue could be in validating the metadata, I hope you did not touch settings like validateExternalMetadata, etc.Arthur My Blog

Hi Koen, Thank you for trying to help me. However, I have to use a stored procedure because my code handles many select statements and many conditions. When I set FMTONLY ON right after the "AS" keyword in the stored procedure, the stored procedure runs fine, but the "Refres Fields" button of the dataset of the report complains about the temporary tables (#tempTable) that I use in the stored proceedure. Would you know any other workaround? How to fix this problem? PS_ I have posted my question here in this SSIS forum because I am using BIDS to create my reports. I am not sure if the issue stems from the report itself or from SSIS environment. Hopefully I will get a satisfying answer soon. Thanks again.

This article might help: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/MCTS, MCITP - Please mark posts as answered where appropriate.

Hi Koen, Thank you for trying to help me. However, I have to use a stored procedure because my code handles many select statements and many conditions. When I set FMTONLY ON right after the "AS" keyword in the stored procedure, the stored procedure runs fine, but the "Refres Fields" button of the dataset of the report complains about the temporary tables (#tempTable) that I use in the stored proceedure. Would you know any other workaround? How to fix this problem? PS_ I have posted my question here in this SSIS forum because I am using BIDS to create my reports. I am not sure if the issue stems from the report itself or from SSIS environment. Hopefully I will get a satisfying answer soon. Thanks again. The line about FMTONLY ON is significant in terms it is not in SQL Server 2012. And it must not be part of the stored proc.Arthur My Blog

Arthur, No, I did not change any settings in BIDS. Everything is at their default state. There is one parameter of type "Date" in my report that is being passed to the stored proceedure. I do not know if it has something to do with the error message. What else to do to fix this annoying error that prevents me from moving on with this project?

Koen, You rock! It worked for me! The solution I have found by following the link you provided, http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/65112/, totally worked for me. I spent almost a week tweaking with the report design in BIDS in order to pull the dataset from the stored porcedure, but in vain. You have put me back on track with my project. Thank you very much! I also thank Arthur for his competent help. Now to those developers that are facing the same problem and who do not want to read through the article, let me jot a brief description on the issue itself and the steps to take to solve this problem: In a report design from within BIDS, fields are provided to the dataset via a stored procedure that receives a parameter value. A "Timeout expired" error message pops up when trying to refresh the fields for the report. It may be caused due to temp tables, such as #tempTable, used in the stored procedure. The fix: 1) Do not use "SET FMTONLY OFF" because it severely degrades (5 times) the stored procedure's performance, and in many cases it does not solve the problem. 2) Using table variables might fix the problem, but it consumes time and requires more coding, and may affect performance. 3) Instead, at the very beginning of the stored procedure, right after "AS ---> SET NOCOUNT ON;" insert a piece of code similar to this one below. It is a simple "SELECT" statement with no "FROM". The name of the fields, must match up the name of the fields that will be returned by the stored procedure, that is the fields that will show up in the report design. Also the data types must match up. IF 1 = 2 BEGIN SELECT CAST(NULL AS DATE) AS Order_Date, CAST(NULL AS INT) AS Order_ID, CAST(NULL AS NVARCHAR(250)) AS SUPERVISOR_FULL_NAME, CAST(NULL AS NVARCHAR(250)) AS SALESPERSON_FULL_NAME, CAST(NULL AS NVARCHAR(250)) AS CUSTOMER_FULL_NAME, CAST(NULL AS DECIMAL(8,1)) AS Itm_Price END From a technical point of view, I do not fully understand the reason behind such a solution, but it works! Thank you!

The reason why it works is because the parser will look for the first SELECT statement in the stored procedure and execute it to find the metadata. It will find the one in the IF statement and it runs pretty fast of course, although it is never executed at runtime. MCTS, MCITP - Please mark posts as answered where appropriate.