none
ExecuteReader requires an open and available Connection. The connection's current state is closed.

    Question

  • I am accessing SQL2005 with C# code using  OleDbConnection.

     

    A try and catch block catches the following error once a while between the Open() and Close() of the connection:

    ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

     

    I do not even have any idea where to start to debug this.  The ExecuteNonQuery() runs a delete SQL query. It works 99.9% of the time. I do not see anything wrong when this error happens. 

     

    Any hint would be greatly appreciated.

     

     

    Thursday, April 26, 2007 2:28 AM

Answers

  • Consider the connection you have to not be guaranteed for the following reasons:

     

    1.) SQL server will hang up on you if you exceed the agreed upon timeout between requests.

    2.) If you are non-local, the connection may be broken and might need to be re-established.

    3.) You may not have successfully connected the first time (maximum number of connections exceeded).

     

    The utility of this exception is that it gives you the opportunity to handle these cases cleanly. 

     

    Catch the exception, and then reopen the connection if this happens.  If the reopen fails -- your network is down or the server is ignoring you. If the server is ignoring you and you know that you are the only process that should be accessing the server, look for connection leaks to make sure that you aren't leaking connections.

     

    I hope this helps,

     

    John (MSFT)

    Friday, April 27, 2007 12:07 AM
    Moderator

All replies

  • Consider the connection you have to not be guaranteed for the following reasons:

     

    1.) SQL server will hang up on you if you exceed the agreed upon timeout between requests.

    2.) If you are non-local, the connection may be broken and might need to be re-established.

    3.) You may not have successfully connected the first time (maximum number of connections exceeded).

     

    The utility of this exception is that it gives you the opportunity to handle these cases cleanly. 

     

    Catch the exception, and then reopen the connection if this happens.  If the reopen fails -- your network is down or the server is ignoring you. If the server is ignoring you and you know that you are the only process that should be accessing the server, look for connection leaks to make sure that you aren't leaking connections.

     

    I hope this helps,

     

    John (MSFT)

    Friday, April 27, 2007 12:07 AM
    Moderator
  • Thanks, John.

     

    I am not sure what you mean by  1.) SQL server will hang up on you if you exceed the agreed upon timeout between requests.  My try-catch blocks have caught timeout exceptions numerous times.  I do not know what you mean by "exceed the agreed upon timeout between requests".

      

    Yes, I am non-local. However, the application has multiple threads and other threads do not have any connection problem.  I am not sure what you mean exactly by  "the connection may be broken".  Do you mean the network connection is broken?  I have had network connection broken many times and I have compliment SQL server for restoring the connection automatically and gracefully once the network connection is reestablished.  For example, if the computer running SQL server is shut down while applications on other compuers are accessing data, the applications will generate exceptions but they just need to be caught and the applications can resume without any problem once the server reboots.  This is not the case for this error.

     

    The query is in a loop that runs all day long. I am certain the initial connection always succeeds.  

     

    I am going to log more information to see this daily error correlates with other activities.

    Friday, April 27, 2007 1:34 AM
  • Hi hipswich,

     

    Hrmm, I agree that it is a good idea to do some client logging around this problem.  Keep the date/time and the sql command you are trying to run (ideally also the last command executed and it's execution time).  You can then cross check these logs against the server to see what is going on. In the mean time, to keep your program running despite this issue, you can catch the exception, reconnect, and execute your command again to keep your app running.  If that command keeps getting hung up on, then it will probably be easy to trace the problem from there (you could even hook in sql profiler to see what it is doing and why it is getting disconnected).

     

    Hope this helps!

     

    John (MSFT)

    Friday, April 27, 2007 6:40 PM
    Moderator
  • John,

     

    I have realized your first response was right!  This error is always preceded by a [DBNETLIB] General network error.   The way my application works (long story) makes the network error show up many hours before this error.

     

    Thanks again!

    Friday, May 18, 2007 10:33 AM
  • I saw this error when working with SQL 2008 SSIS.  Strangely, I resolved it by closing my integration services package and reopening it.  I had left my RDP session open overnight since I'm building the packages right on the test server.  I'm not saying this a solution necessarily, but it fixed the problem.  Someone else might be able to explain why.  :-)
    Thursday, May 21, 2009 7:22 PM
  •  

    how to display ms access file in gui? 

    i have established connection using the following steps:


     

     

  • In the view menu click on the server explorer
  • The server explorer is open 
  • Then right click data connection
  • Add new connection
  • Follow the widzard steps
  • Once the connection is established 
  • The DataBase appears with all its tables and views in form of tree
  • Select one table node 
  • Expand the table node
  • You will see the fields 
  • You just select them all and then drag and drop them into you page that's all

     

     

     

    • Proposed as answer by prabu17jan88 Sunday, September 26, 2010 9:08 PM
    Sunday, September 26, 2010 8:14 PM
  • Consider the connection you have to not be guaranteed for the following reasons:

     

    1.) SQL server will hang up on you if you exceed the agreed upon timeout between requests.

    2.) If you are non-local, the connection may be broken and might need to be re-established.

    3.) You may not have successfully connected the first time (maximum number of connections exceeded).

     

    The utility of this exception is that it gives you the opportunity to handle these cases cleanly. 

     

    Catch the exception, and then reopen the connection if this happens.  If the reopen fails -- your network is down or the server is ignoring you. If the server is ignoring you and you know that you are the only process that should be accessing the server, look for connection leaks to make sure that you aren't leaking connections.

     

    I hope this helps,

     

    John (MSFT)


    i m using this code and getting same error .plz let me know where is the error.one more thing the same program run successfully in my another computer.  

     

    public bool LoginValid(string user, string pswd)

    {

     

     

    string password;

    scom =

    new SqlCommand("DeptLogin", scon);

    scom.CommandType =

    CommandType.StoredProcedure;

    scom.Parameters.AddWithValue(

    "@username", SqlDbType.NVarChar).Value = user;

    scom.Parameters.AddWithValue(

    "@pswd", SqlDbType.NVarChar).Value = pswd;

     

     

    SqlDataReader sdar;

     

     

    //int c=int.Parse(scom.ExecuteReader().ToString());

     

    sdar = scom.ExecuteReader(); //getting error here

     

    try

    {

     

     

    bool i = sdar.Read();

     

    password = sdar[1].ToString();

    }

     

    catch (Exception e)

    {

     

    return false;

    }

     

    if (password == pswd)

    {

     

    Label lbl = new Label();

    lbl.Text =

    "Login Successful";

    }

     

     

    return true;

    }

    Sunday, March 06, 2011 6:14 PM
  • My solution was the same as yours AtomicPunk1000.  I closed Visual Studio and opened my SSIS solution again and I was good.  Good suggestion.
    Thursday, April 28, 2011 8:42 PM