Time Out Expired When Refreshing Fields in BIDS
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.
June 5th, 2012 5:08pm

Delete and re-create this data source and make sure it retains the same name.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
June 5th, 2012 9:13pm

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?
June 7th, 2012 3:15pm

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
Free Windows Admin Tool Kit Click here and download it now
June 7th, 2012 3:28pm

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.
June 7th, 2012 3:28pm

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
Free Windows Admin Tool Kit Click here and download it now
June 7th, 2012 3:52pm

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?
June 7th, 2012 5:00pm

Hi Koen /AmirBabazadeh, I used solution mentioned in AmirBabazadeh blog and it solved my timeout problem. Earlier I was using stored proc for my report and at first it worked fine but when i tried to attach updated stored proc to report then report time out problem started and I don't understand why suddenly it start giving timeout issue. But above solution work for me so thanks guys
Free Windows Admin Tool Kit Click here and download it now
July 18th, 2012 4:02pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics