Rdlc Timeout

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!
nmiller61Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pushpakumara MahagamageVPCommented:
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.
0
nmiller61Author 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?
0
Pushpakumara MahagamageVPCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

nmiller61Author 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.
0
Pushpakumara MahagamageVPCommented:
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.
0
nmiller61Author 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.
0
Pushpakumara MahagamageVPCommented:
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");
}
0
nmiller61Author 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.
0
Pushpakumara MahagamageVPCommented:
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
1
nmiller61Author Commented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nmiller61Author Commented:
It fixed my problem.
0
Pushpakumara MahagamageVPCommented:
Hey,

 You have accepted your own comment instead of mine. :)  You are welcome.
0
nmiller61Author 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?
0
Pushpakumara MahagamageVPCommented:
No Worries.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.