Peter's Gekko

Sponsors

The Lounge

Wicked Cool Jobs

Syndication

Recent Posts

Tags

View more

News

Links

Archives

Advertisement

Peter's Gekko » Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.

When your app runs into a timeout on a database operation the error message may lead you in a wrong direction to fix things. Which happened to me so this is my little index to the docs.

A sqlConnection has a ConnectionTimeout property. It  is a property which determines the maximum number of seconds your code will wait for a connection to the database to open. It is set in the connectionstring to the database

data source=BROCHIS;initial catalog=Indato;integrated security=SSPI;Connect Timeout=60;

Opening the connection does not do anything in the database yet. That is handled by a SqlCommand, which could be part of a SqlDataAdapter. The SqlCommand has a CommandTimeOut. This is a settable property which determines the maximum number of seconds the command is given to execute the sql. By default this is 30 seconds. When you perform something costly in the database this is the one whose value you'll have to change.

SqlCommand cmd;

.....

if (bigJob)

   cmd.CommandTimeout = 60;

For both properties a value of 0 indicates to wait forever. Both properties also apply to non sqlserver database operations. They are defined in IdbConnection and IdbCommand.

Posted Mon, Mar 20 2006 9:03 AM by pvanooijen
Filed under:

[Advertisement]

Comments

Christopher Steen wrote Link Listing - March 21, 2006
on Wed, Mar 22 2006 12:18 AM
ACE Threat Modeling
[Via: Keith Brown ]
AJAX Myth - I love it............. [Via: Wallym ]
Article:...
Simon Munro wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Wed, Mar 22 2006 8:55 AM
Around the time .NET 1.1 had just come out, I was on a project where we needed a long timeout for our commands due to the nature of the architecture (large databases with back-end tasks that prefetched data for processing).  The CommandTimeout property is set arbitrarily at 60s and we needed more... across all our commands.  The property couldn't be set easily in a .config file.  

Easy solution, in an OO sense, descend from SqlCommand and set the timeout in the constructor... not possible because SqlCommand is sealed (not inheritable) and we had to hand code (or generate) the timeout in every command in our DAL - a real pain.

The timeout works well... I just wish a simple OO concept such as inheritance was actually allowed on the SQLCommand class to make it easier to use across the entire project.

Khuzema wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Sat, Mar 25 2006 8:51 AM
Thanks
Peter's Gekko wrote Timeout of an ASP.NET page
on Thu, Jun 15 2006 8:33 AM
In some cases it can take quite some for the web server to complete your webpage. Setting a time out...
Peter's Gekko wrote A long running SQL batch in asp.net with feedback
on Tue, Jun 20 2006 4:14 AM
Another story from the app with the sprocs. That was a classical 2-tier CS application which we transformed...
Gurjinder Singh Brar wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Fri, Dec 29 2006 2:37 AM

Yes, you are right this CommandTimeOut error also exist in SQLHelper.cs class.

To avoid this problem use

cmd.CommandTimeout=con.ConnectionTimeout;

Thanks,

Gurjinder Singh Brar

http://agileguru.blogspot.com

pvanooijen wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Fri, Dec 29 2006 4:14 AM

Not always. What if several commands have to complete on one conection on one roundtrip ?

The formula would be con.ConnectionTimeout = cmd1.CommandTimeout + cmd2.CommandTimeout + ......

ZAZe wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Mon, Jun 25 2007 4:24 AM

No way

John Halliday wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Tue, Apr 29 2008 1:35 PM

Thanks for the simple explanation of timeout settings.  

I have been working to find an answer to my own connection timeout problem for some time.  I run a 4 hour stored procedure and it keeps timing out after just over an hour.  I have the command timeout set to:

     da.SelectCommand.CommandTimeout = 900000;

... this times out after about 80 minutes.  After reading your article I altered the connection string to include the same timeout amount and re-ran.  It timed out after 2 hours this time (better!) with:

"System.Data.OleDb.OleDbException: Timeout exceeded.\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForMultpleResults(tagDBPARAMS dbParams, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)\r\n   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)\r\n   at ARTS_Billing.Bill_Main.GetBills(String BillType, Int32 BEG_APPL_ID, Int32 END_APPL_ID) in C:\\ARTS_Project\\Code\\Utilities\\Billing\\ARTS_Billing\\ARTS_Billing\\Bill_Main.cs:line 444"

Thanks again for the hints.

pvanooijen wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Tue, May 13 2008 3:53 AM

Looking at your stacktrace you have resultsets. Why do you need a result at all when it takes so long. Imho it would be a better approach to fire off your sproc with an executeNonQuery.

And check the results after some time, like explainded here :

codebetter.com/.../146446.aspx

rowi wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Mon, Jun 9 2008 10:00 AM

THX, that helped me out :-)

Jasmine wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Thu, Jul 17 2008 5:53 AM

Hey thanks so much! Have been stuck with this issue for quite sometime now.. Finally your code made it work! :)

Fish wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Fri, Jul 25 2008 12:25 AM

Thanks

Rowan wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Fri, Aug 1 2008 12:04 AM

Would the SqlCommand object inherit the time out settings if the SqlCommand's CreateCommand method is used?

Example:

SqlConnection conn = new SqlConnection(...);

SqlCommand cmd = conn.CreateCommand();

I don't have .NET 1.1 compiler right now, so I can't test it out.

Avinash wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Mon, Aug 18 2008 4:36 AM

Hi all,

I am also having similar problem. I am getting data from the SYBASE database using Sproc. Problem is the SProc need more than 5 hrs to execute depending on the parameters. Its always giving timeout after 5 hrs with following exception.

"Method: ExecuteCommandTextForMultpleResults; Cause: Timeout exceeded. "

Could any one please help me with this? Thanks in advance.

Prashantha wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Tue, Oct 21 2008 5:35 AM

What are the impacts of setting a commantimeout to its maximum value i.e: 65535 seconds.?

pvanooijen wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Tue, Oct 21 2008 7:06 AM

65536 seconds ? That's over 18 hours !

Expect the webrequest using the sql connection to time out or the user using the application involved to have walked away by the time that times out :)

Kamal Satkunanathan wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Mon, Dec 22 2008 11:11 AM

Thanks, you helped me.

A dude wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Wed, Mar 18 2009 1:30 PM

Thanks man

Jportela wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Thu, Mar 26 2009 3:36 PM

Hello:

I am trying to connect to a ORACLE using the OleDbCommand in .NET 2.0 from a windows app. I've set the CommandTimeOut property to 10 (10 secoonds) but the timeout never comes out, it expects for ever, any hints?

dataCore wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Fri, May 29 2009 10:20 AM

is there a way to set the command timeout in the storedprocedure/query which will be executed?

in my opinion not - cause the sqlqueryexecuterobject handels the timeout... but maybe?!? :)

Ashish wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Mon, Jun 29 2009 1:48 AM

I am getting "No suitable Node To Server Your Request " as my SQL Command exceed Time  with one minute only...

Any solution for same..??

pvanooijen wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Mon, Jun 29 2009 3:22 AM

@Ashish: Check this : forums.asp.net/.../1429865.aspx (first hit in google)

@datacore. I guess you're right The way to really be suie is of course to try.

@Jportella. Don't know, these options were part of the sqlcommand which talks specific to sql server. How the Oracle ole-db provider implements the matters could be different.

Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut. « ??? wrote Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut. « ???
on Fri, Oct 16 2009 9:27 PM

Pingback from  Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.  « ???

Tim! wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on Fri, Feb 26 2010 7:32 PM

@Simon Munro: Create a factory method:

class SqlHelper

{

 public static SqlCommand createCommand()

 {

   SqlCommand cmd = new SqlCommand();

   cmd.CommandTimeout = 180;

   return cmd;

 }

}

Then wherever you have

SqlCommand foo = new SqlCommand();

use instead

SqlCommand foo = SqlHelper.createCommand();

You could override createCommand with all the SqlCommand constructor overrides if you use those.

Add a Comment

(required)  
(optional)
(required)  
Remember Me?
Devlicio.us