none
Conn.Open() still work after SQL Service is stopped

    Question

  • I have met a issue with sql server 2005 SP2, I have created a windows form and with a button on it, and with the following steps:

    1. Make sure Sql service is running, then click the button, everything is OK
    2. Stop the Sql service, and then click the button again, on my machine, there is not exception at the code of LINE 1, exception occurred at LINE 2, and this is the exception info:

    Message: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

    using System; 
    using System.Collections.Generic; 
    using System.ComponentModel; 
    using System.Data; 
    using System.Drawing; 
    using System.Text; 
    using System.Windows.Forms; 
    using System.Data.SqlClient; 
     
    namespace ReconnectSQL 
    { 
      public partial class Form1 : Form 
      { 
     
        private string m_ConnectionString = @"Server=(local); Database=testDB; User ID=sa; Password=admins; Connection Timeout=15"; 
     
        public Form1() 
        { 
          InitializeComponent(); 
        } 
     
        /// <summary> 
        /// 
        /// </summary> 
        public DataTable GetByFillDataTable() 
        { 
          try 
          { 
            SqlCommand cmd = new SqlCommand("getalldata"); 
            cmd.CommandType = CommandType.StoredProcedure; 
     
            DataTable dt = this.GetDataTable(cmd); 
            return dt; 
          } 
          catch 
          { 
            throw; 
          } 
        } 
     
     
        #region common funcs 
        /// <summary> 
        /// 
        /// </summary> 
        /// <param name="cmd"></param> 
        /// <returns></returns> 
        private DataTable GetDataTable(SqlCommand cmd) 
        { 
          DataTable dt = new DataTable(); 
     
          using (SqlConnection conn = new SqlConnection(this.m_ConnectionString)) 
          { 
            try 
            { 
              conn.Open(); // LINE 1 
            } 
            catch (Exception eX) 
            { 
              throw; 
            } 
     
            using (SqlDataAdapter adapter = new SqlDataAdapter()) 
            { 
              try 
              { 
                cmd.Connection = conn; 
                cmd.CommandTimeout = conn.ConnectionTimeout; 
                adapter.SelectCommand = cmd; 
                adapter.Fill(dt); // LINE 2 
              } 
              catch (Exception eX) 
              { 
                throw; 
              } 
            } 
          } 
     
          return dt; 
        } 
     
        #endregion    
     
        private void button2_Click(object sender, EventArgs e) 
        { 
     
          try 
          { 
            DataTable dt = GetByFillDataTable(); 
            listBox1.Items.Add("GetByFillDataTable is called without exceptions!"); 
          } 
          catch (Exception ex) 
          { 
            listBox1.Items.Add(ex.Message); 
          }      } 
      } 
    } 
    

    Detailed exception info:

    -       [System.Data.SqlClient.SqlException]    {"A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"} System.Data.SqlClient.SqlException 
    +       base    {"A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"} System.Data.Common.DbException {System.Data.SqlClient.SqlException} 
            Class   20  byte 
    +       Errors  {System.Data.SqlClient.SqlErrorCollection}  System.Data.SqlClient.SqlErrorCollection 
            LineNumber  0   int 
            Number  233 int 
            Procedure   null    string 
            Server  "(local)"   string 
            Source  ".Net SqlClient Data Provider"  string 
            State   0   byte 
    

    StackTrace

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) 
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 
       at System.Data.SqlClient.TdsParserStateObject.WriteSni() 
       at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode) 
       at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush() 
       at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) 
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) 
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) 
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) 
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) 
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) 
       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) 
       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) 
       at ReconnectSQL.Form1.GetDataTable(SqlCommand cmd) in E:\_public_\sqlFail\ReconnectSQL
    • Moved by Bob BeaucheminMVP Tuesday, November 16, 2010 7:33 AM Moving to a more relevent forum (From:.NET Framework inside SQL Server)
    • Moved by Kira Qian Wednesday, November 17, 2010 6:15 AM (From:Windows Forms Data Controls and Databinding)
    Tuesday, November 16, 2010 3:18 AM

Answers

  • Yes, the connection will still be in the pool. There is no notification of the connection being closed sent to the pool.
    You can only see if the connection is closed by trying to use it (and get the failure).

    SQL Server Connection Pooling (ADO.NET)
    http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

    "The connection pooler removes a connection from the pool after it has been idle for a long time,
    or if the pooler detects that the connection with the server has been severed.
    Note that a severed connection can be detected only after attempting to communicate with the server."

    You can possibly use the retry logic described here.

    SQL Server Best Practices Article
    http://technet.microsoft.com/en-us/library/cc917713.aspx


    This posting is provided "AS IS" with no warranties.
    Wednesday, November 17, 2010 10:03 AM

All replies

  • At step 1) the connection is opened. After verifying that the SQL Server is working fine, I don't see that you are closing the connection.

    Hence when you click the button again at Step 2), for the application the connection is still open, since the SQL Server service is stopped it throws an error while trying to the populate the details.

    Put Conn.close at the end of the code, you would see the exception will be raised at Conn.open during the second step.




    Pradeep Adiga
    My blog: http://www.sqldbadiaries.com

    Recent posts on my blog
    Tuesday, November 16, 2010 5:22 AM
  • Since I put conn object in the using blocks, I do not think I should use conn.Close() to close the connection.

    After some investigations, it seems the connection still exist in the connection pool even sql is stopped, so after sql is started and by calling conn.Open() it get the connection object from the pool which should be invalid, then the SqlDataAdapter.Fill cause the exception,  so is this a bug of SQL Service? or is there any other method to fix the exception?

    Tuesday, November 16, 2010 8:18 AM
  • Yes, the connection will still be in the pool. There is no notification of the connection being closed sent to the pool.
    You can only see if the connection is closed by trying to use it (and get the failure).

    SQL Server Connection Pooling (ADO.NET)
    http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

    "The connection pooler removes a connection from the pool after it has been idle for a long time,
    or if the pooler detects that the connection with the server has been severed.
    Note that a severed connection can be detected only after attempting to communicate with the server."

    You can possibly use the retry logic described here.

    SQL Server Best Practices Article
    http://technet.microsoft.com/en-us/library/cc917713.aspx


    This posting is provided "AS IS" with no warranties.
    Wednesday, November 17, 2010 10:03 AM
  • Hi Carlos, 

    This is a common mistake. When you use a using block in combination with a SqlConnection, that connection will not be GC when there is an exception. You always need to wrap the using with a try_finally construct where you explicitly close the connection (after a check if it's open or closed!)

    Kind regards,
    Tom de Koning


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, November 17, 2010 1:22 PM