Peter's Gekko

Sponsors

The Lounge

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 03-20-2006 9:03 AM by pvanooijen
Filed under:

[Advertisement]

Comments

Christopher Steen wrote Link Listing - March 21, 2006
on 03-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 03-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 03-25-2006 8:51 AM
Thanks
Peter's Gekko wrote Timeout of an ASP.NET page
on 06-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 06-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 12-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 12-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 06-25-2007 4:24 AM

No way

John Halliday wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on 04-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 05-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 06-09-2008 10:00 AM

THX, that helped me out :-)

Jasmine wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on 07-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 07-25-2008 12:25 AM

Thanks

Rowan wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on 08-01-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 08-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 10-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 10-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 12-22-2008 11:11 AM

Thanks, you helped me.

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

Thanks man

Jportela wrote re: Timeout on a (sql) database: SqlConnection.ConnectionTimeOut and SqlCommand.CommandTimeOut.
on 03-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 05-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 06-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 06-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.

Add a Comment

(required)  
(optional)
(required)  
Remember Me?