Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I’m been facing this timeout problem in my report query especially for rdlc report where the application need to provide the datasource for the report. The error prompt more frequently when the number of records increased in table, so most of time you won’t have this timeout error during your development or early stage of production. But when when production data increasing you will facing timeout error.
There is 2 thing i can suggest here to avoid this problem. First keep your query or view simple and fast enough. What i mean here is make sure you use the correct joins in query if you ever need one and only put the fields that you need in query. Number of column if too many in your query or view will also make the execution time longer. But sometimes we can’t avoid creating a query or view that take so much memory and execution time so when you arrive on this situation its a must to set the timeout property to longer time.
There are two main Timeout property in ADO.NET.
1. Connection Timeout for Connection
2. Timeout for Data Access( Command Object or DataAdapter)
How to set Connection Timeout property for sql connnection object ?
You can’t set timeout property in connection object because its a read only property
// you cannot do this
SqlConnection conn = new SqlConnection("server=Server;uid=sa;pwd=123456;database=myDB;Connection");
conn.ConnectionTimeout = 100;
The correct way to do this is set the timeout value in connection string like the example below.
// the correct way to set connection timeout
SqlConnection conn = new SqlConnection("server=Server;uid=sa;pwd=123456;database=myDB;Connection Timeout=90");
Next , How to set timeout property in DataAdapter or Command Object ?
Its pretty simple as shown below.
SqlDataAdapter sqlAdapter = new SqlDataAdapter(); sqlAdapter.SelectCommand = new SqlCommand(sqlQuery, conn); sqlAdapter.SelectCommand.CommandTimeout = 0;
Setting the timeout value to 0 will makes adapter or command to wait for indefinite time before terminating the attempt to execute a command and generating an error.
The default timeout value for connection object is 15 seconds.
The default timeout value for DataAdapter or Command Object is 30 seconds
If you have better ways to overcome this timeout problem please leave your comments. It will be very helpful 
(2 votes, average: 3.00 out of 5)
Loading ...
I am an organism that turns coffee into software 
12 responses to "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
[...] my previous post I have suggested to increase the timeout value to prevent the ” Timeout expired. The timeout period elapsed prior to completion of the operation or the server is no… ” error. But after set the timeout value to 600 seconds I still get errors but this time [...]
[...] – bookmarked by 6 members originally found by lbalves on 2008-08-17 Timeout expired. The timeout period elapsed prior to completion of … [...]
Bala,
You saved my day!! i wish i had come across this article much earlier.
thanks dude…that worked for me…
Not the direction I’d go…
You NEVER want to hard-code a connection string or any connection parameters anywhere in code, as this violates both Security Policy and Good Programming Practice (as in the first example, which clearly shows the database name and SysAdmin account credentials in the clear…I hope no one actually names their SysAdmin account “SA” with a password of “123456” ).
You need to use an encrypted “data key” connection string (3DES encrypted binary, 128-bit certificate, etc.). This allows you to instantiate the connection object with and encrypted string while hiding all parameters (even the location of the database). After all, all I need to brute-force your database is the name of the database (the timeout just helps the hack, so I can use a planed latency brute-force attack every 90 seconds – of course, your account credentials are nice too!) This is also why Microsoft designed the ASP.NET Connection String opting in IIS – no coding! Although, this still exposes credentials to unwanted SysAdmin eyes…Better to encrypt and deploy by the DBA.
This option allows you to dynamically change & create various “data keys” and load them at run-time as needed – maintaining a strong security policy, good programming practice and allowing the DBA to configure as desired in multiple parallel instances. Not to mention, keeping the primary responsibility of connection orientation and account authorization with the Data Architect\DBA and NOT a programmer or SysAdmin – No one but the DBA see’s the credentials!
Where would I set the SelectCommand.CommandTimeout = 0; is it within the aspx file?
well but setting timeout value will not fix the reason of timeout, isn’t it?
i have also same problem & then i had set command.timeout=60 but it again fire same error give me another alternative
Franklin , happy and bhavna ,
Set the timeout in connection string and command object. Check the code in above post.
If your developing ASP.Net application then check this post
Try this:
sqlAdapter.SelectCommand.CommandTimeout=conn.ConnectionTimeout
then you can control the timeouts of all your adapters with the connection time out setting in the connection string.
I would not use a commandtimeout of 0, but a bit longer than the default 15 secs can be useful for long running stored procs
Thanks, its works for me.
Have a great day!!
Thanks alot,
I was struggling with this timeout problem….
Leave a comment