Geeks With Blogs
BizTalk Blog by Chris Han System Design for Enterprise Agility,

I got this error when testing my web service POC which is runing on .netFx 2.0:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

I find Angel's article gives the most accurate diagnosis. Check it out here:
http://blogs.msdn.com/angelsb/archive/2004/08/25/220333.aspx

But the solution he gives out doesn't solve the problem:
      public void DoesNotLeakConnections()

      {          

                  Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5")) {

                              sqlconnection1.Open();

                              SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                              sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";

                              sqlcommand1.ExecuteNonQuery();  //this throws a SqlException every time it is called.

                              sqlconnection1.Close(); //Still never gets called.

                  } // Here sqlconnection1.Dispose is _guaranteed_

      }


What you should do which is the best practice for any resource related job is to close the resource in a Final block like this:

 

      public void DoesNotLeakConnections()  

      {          

                  Using (SqlConnection sqlconnection1 = new SqlConnection("Server=.\\SQLEXPRESS ;Integrated security=sspi;connection timeout=5")) {

try

{

                              sqlconnection1.Open();

                              SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();

                              sqlcommand1.CommandText = "raiserror ('This is a fake exception', 17,1)";

                              sqlcommand1.ExecuteNonQuery();  //this throws a SqlException every time it is called.

}

catch  (Exception ex)

{

  //do something

}

finally

 

 

{

          sqlconnection1.Close(); //Always gets called.

}

                  } // Here sqlconnection1.Dispose is _guaranteed_

      }


 

Posted on Thursday, June 28, 2007 9:49 AM SQL Server | Back to top


Comments on this post: Error: System.Data.SqlClient.SqlException: Timeout expired

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
But the implementation of Dispose DOES call Close, which is the recommended pattern for IDisposable implementations. So adding the extra try/finally does not accomplish anymore in reality, except that you've caught the exception.
Left by Paul Wilson on Jun 28, 2007 11:16 AM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
errrr, it turned out is because SqlCommand.CommandTimeout Property by default 30s. I only set connecition time out.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
Left by chris on Jul 05, 2007 4:39 PM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
I need to continue my work urgently.Please,I need help.
Left by william navarrete on Jan 14, 2009 9:15 PM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
what's the problem, william?
Left by chris on Jan 15, 2009 9:07 AM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
i am facing the same problem...please give me an idea to resolve it..
Left by shubham saxena on Mar 15, 2009 2:48 AM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
i meet this problem too. pls help me!!!
Left by Ariya on Apr 13, 2010 1:26 AM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
I'm korean. It will help to you.
But It's C# Code below.

SqlDataAdapter adapter = new SqlDataAdapter(strSQL, Constr + "; Connect Timeout = 120")
adapter.SelectCommand.CommandTimeout = 120;
Left by Dong woon Song on May 11, 2010 3:55 PM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
You wanker, did you leave this post up for google hits?
This is wrong for the exact reasons that paul wilson wrote.
Left by Choco on Feb 16, 2011 4:57 AM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
Also i forgot, stop catching general exceptions. Check out FxCop, you obviously need coding help
Left by Choco on Feb 16, 2011 4:58 AM

# re: Error: System.Data.SqlClient.SqlException: Timeout expired
Requesting Gravatar...
Chris, are you sure the using statement won't close the connection if any exception prompts in its block? Could you help detail the way to verify?

Joshua
Left by Joshua on Nov 25, 2011 1:59 AM

Your comment:
 (will show your gravatar)


Copyright © Chris Han | Powered by: GeeksWithBlogs.net