Rdlc Timeout

nmiller61
nmiller61 used Ask the Experts™
on
I have been scratching my head on this for awhile now. I have a website that I have local RDLCs that are populated by an ObjectDataSource which is pulling in TableAdapters from an Xsd data source. I am getting the below error after a report takes longer than 30 seconds to run:
An error has occurred during report processing.
Cannot create a connection to data source 'xxxxx'.
Exception has been thrown by the target of an invocation.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The wait operation timed out

How do I extend this. I have done research and found to try manually building the data source in the code but I have not successfully gotten that to work. But I also found to put a 'Connect Timeout' or 'Connection Timeout' or even just 'Timeout' in the connection string in the web.config file but no matter how large of a value I set after 30 seconds it times out. Here is what my connection string looks like:
<add name="xxxxx" connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=xxxxx;initial catalog=xxxx;integrated security=True;Timeout=60000;Connect Timeout=60000;Connection Timeout=60000;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
     
Any help would be GREAT!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Have you setup executionTimeout in your web config, default is

<httpRuntime executionTimeout = "number(in seconds)"/>

https://stackoverflow.com/questions/10649843/how-to-increase-executiontimeout-for-a-long-running-query 
https://forums.asp.net/t/1040377.aspx?How+to+increase+the+time+out+for+request+response+

and check same on DB server side. i mean execute same query in database and check how long it take to show results.

Author

Commented:
Thank you Pushpakumara for your reply. I did set the executionTimeout in my web config file and it did not change my time out time. Still after 30 seconds it times out. I ran the query in my database and it takes about 60 seconds to complete. I did not see any other answer in those other posts. Any other suggestions?
In <system.web> section
 <httpRuntime executionTimeout="3600" maxRequestLength="102400"/>

end of the  connection string set connection timeout

Data Source=MYDBServer\SQL2016;Initial Catalog=mydb;User ID=someone;Password=blank;Connection Timeout=300

Have you set command timeout ---
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396


I had the same issue in SQL server ASP.net entity framework environment, I'll check what I have done to fix the issue. and let you know.

and 60 second is quite big value for load data on Database server, so check your query also.

try to move your application side query to stored procedure. that will give you result quickly.   if SP is also taking long time only because of huge data load , you may have to use BCP.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I did try your suggestions. Except the Sql Command Timeout since my report gets populated by an ObjectDataSource object that is populated by a SP from an .xsd file. My report still times out after 30 seconds. Anything else to try? I cannot believe 30 seconds is as long as it can be.
Command time out is also important. 30 sec is default value, you can define command time out at your table adaptor  and add a public property to set/get the command timeout value.

CommandTimeout = 120

do some google search against " ObjectDataSource  TableAdapters Command time out"

https://forums.asp.net/t/2009515.aspx?CommandTimeout+property+for+Table+Adapter+or+ObjectDataSource
https://www.codeproject.com/Articles/13009/Accesing-CommandTimeout-properties-in-a-TableAdapt

For my case DB developer optimized the SQL Query to execute in less than 10 sec, adding index and archiving old data. removing some joint etc.

Author

Commented:
I guess in my situation I am not sure how to set the CommandTimeout. The example URLs do not really tell me what I am looking for. I have a ReportViewer object that is tied to an ObjectDatasource object that is tied to a Stored Procedure TableAdapter that takes parameters off my form to run the report. In this situation there is NO C# code on this page that does anything. If I am supposed to set the CommandTimeout for my TableAdapter, HOW do I do that in this situation?

The examples may show how to set it for a tableadapter but not execute the tableadapter or add that tableadapter to an objectdatasource object.
You have to put command timeout at your application code where you call the SP. you can't put command time out in your web.config

CommandTimeout = 0  is no time out. but put some resonable time in seconds like CommandTimeout = 120


-------Code ------

 using (SqlConnection connection = new SqlConnection(ApplicationDefault.ConnectionString))
            {
                connection.Open();

                SqlCommand command = new SqlCommand("spTest", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 0;
  command.Parameters.AddWithValue("@A", A);
                command.Parameters.AddWithValue("@B", B);
               
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet, "t");
}

Author

Commented:
Sorry for the delayed response but again that does not seem to answer my question. I do not populate my report with calling SQL Commands like that. As I mentioned above I have an .xsd file for the data source. Is there any way around this? I cannot believe that everyone is ok with a 30 second timeout, especially with what I am doing is some complex reporting along with even import data and validating it through this tool.

As for the above, I do not have an ApplicationDefault.ConnectionString to connect to.
Application default connection string mean the connection string you are using in your web.config.  can you put your code, where you call the data might behelp and try to add more topics to your question and then more experts will see your issue and help.

check whether you can do that.
https://www.codeproject.com/Questions/238245/Dataset-xsd-Timeout-Problem

and this issue is also like yours.
https://stackoverflow.com/questions/1192171/how-can-i-change-the-table-adapters-command-timeout

https://stackoverflow.com/questions/21680733/bind-datatable-to-rdlc-and-reportviewer

Regards,

Pushpe
That URL from codeproject is exactly what I was looking for. I hate having to do it from every single report but it WORKS. Thank you so much. I would not of thought timeout setting on this would be so difficult.

I'm sure the kind of thing I am doing is not unique. What does everyone else use for reporting in web applications?

Author

Commented:
It fixed my problem.
Hey,

 You have accepted your own comment instead of mine. :)  You are welcome.

Author

Commented:
Sorry I thought I clicked on your solution. I don't know how I selected my. I was looking to see how to change because my post is not helpful in fixing the problem. How can that be changed?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial