Timeout problem
-
Hi All, I have wrote stored procedure which calculates points for result for around 1000 users. This procedure takes no parameters from asp.net code. asp.net code is used for just for calling this procedure. But when I run this procedure from the code it gives me the timout error. while running from the sql server itself it works fine. Kindly tell me how can I overcome this problem? Below is my code, which is giving me the error. try { Helper connHLP = new Helper(false); connHLP.Retrieve("prcUpdateUsePoints", null); }catch (Exception ex) { throw ex; } ---------------------------------------------------------------------------------------------------------------------- Code for connection & retrive: private SqlConnection conn; private SqlTransaction tran; public Helper(bool TransactionRequired) { try { string strConn = string.Empty; strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString(); conn = new SqlConnection(strConn); conn.Open(); if (TransactionRequired == true) { tran = conn.BeginTransaction(); } else { tran = null; } } catch (Exception ex) { throw ex; } } public DataSet Retrieve(string ProcedureName, SqlParameter[] ParamCollection) { try { DataSet ds = new DataSet();SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; if (ParamCollection != null) SetParameters(cmd, ParamCollection); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProcedureName; SqlDataAdapter adp = new SqlDataAdapter(cmd); adp.Fill(ds); return ds; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } public void SetParameters(SqlCommand cmd, SqlParameter[] ParamCollection) { try { foreach (SqlParameter param in ParamCollection) cmd.Parameters.Add(param); } catch (Exception ex) { throw ex; } } ---------------------------------------------------------------------------------------------------------------------- Thnking you all in advance.
-
Hi All, I have wrote stored procedure which calculates points for result for around 1000 users. This procedure takes no parameters from asp.net code. asp.net code is used for just for calling this procedure. But when I run this procedure from the code it gives me the timout error. while running from the sql server itself it works fine. Kindly tell me how can I overcome this problem? Below is my code, which is giving me the error. try { Helper connHLP = new Helper(false); connHLP.Retrieve("prcUpdateUsePoints", null); }catch (Exception ex) { throw ex; } ---------------------------------------------------------------------------------------------------------------------- Code for connection & retrive: private SqlConnection conn; private SqlTransaction tran; public Helper(bool TransactionRequired) { try { string strConn = string.Empty; strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString(); conn = new SqlConnection(strConn); conn.Open(); if (TransactionRequired == true) { tran = conn.BeginTransaction(); } else { tran = null; } } catch (Exception ex) { throw ex; } } public DataSet Retrieve(string ProcedureName, SqlParameter[] ParamCollection) { try { DataSet ds = new DataSet();SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; if (ParamCollection != null) SetParameters(cmd, ParamCollection); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProcedureName; SqlDataAdapter adp = new SqlDataAdapter(cmd); adp.Fill(ds); return ds; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } public void SetParameters(SqlCommand cmd, SqlParameter[] ParamCollection) { try { foreach (SqlParameter param in ParamCollection) cmd.Parameters.Add(param); } catch (Exception ex) { throw ex; } } ---------------------------------------------------------------------------------------------------------------------- Thnking you all in advance.
The command object has a default timeout of 30 seconds. YOu will need to set this to some sort of timeout that is long enough for the stored procedure to run. If this is a really long running sp you may need to increase some of the web site timeouts as well. Hope that helps. Ben
-
Hi All, I have wrote stored procedure which calculates points for result for around 1000 users. This procedure takes no parameters from asp.net code. asp.net code is used for just for calling this procedure. But when I run this procedure from the code it gives me the timout error. while running from the sql server itself it works fine. Kindly tell me how can I overcome this problem? Below is my code, which is giving me the error. try { Helper connHLP = new Helper(false); connHLP.Retrieve("prcUpdateUsePoints", null); }catch (Exception ex) { throw ex; } ---------------------------------------------------------------------------------------------------------------------- Code for connection & retrive: private SqlConnection conn; private SqlTransaction tran; public Helper(bool TransactionRequired) { try { string strConn = string.Empty; strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString(); conn = new SqlConnection(strConn); conn.Open(); if (TransactionRequired == true) { tran = conn.BeginTransaction(); } else { tran = null; } } catch (Exception ex) { throw ex; } } public DataSet Retrieve(string ProcedureName, SqlParameter[] ParamCollection) { try { DataSet ds = new DataSet();SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; if (ParamCollection != null) SetParameters(cmd, ParamCollection); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProcedureName; SqlDataAdapter adp = new SqlDataAdapter(cmd); adp.Fill(ds); return ds; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } public void SetParameters(SqlCommand cmd, SqlParameter[] ParamCollection) { try { foreach (SqlParameter param in ParamCollection) cmd.Parameters.Add(param); } catch (Exception ex) { throw ex; } } ---------------------------------------------------------------------------------------------------------------------- Thnking you all in advance.
Query Analyzer/Management Studio does not time out. In contrast, ADO.NET does have a default command timeout of 30 seconds. To change this, change the
SqlCommand'sCommandTimeoutproperty. You should consider investigating why this command is taking so long, however. Blocking an ASP.NET worker thread for any significant length of time seriously inhibits the scalability of your website.
DoEvents: Generating unexpected recursion since 1991 -
Hi All, I have wrote stored procedure which calculates points for result for around 1000 users. This procedure takes no parameters from asp.net code. asp.net code is used for just for calling this procedure. But when I run this procedure from the code it gives me the timout error. while running from the sql server itself it works fine. Kindly tell me how can I overcome this problem? Below is my code, which is giving me the error. try { Helper connHLP = new Helper(false); connHLP.Retrieve("prcUpdateUsePoints", null); }catch (Exception ex) { throw ex; } ---------------------------------------------------------------------------------------------------------------------- Code for connection & retrive: private SqlConnection conn; private SqlTransaction tran; public Helper(bool TransactionRequired) { try { string strConn = string.Empty; strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString(); conn = new SqlConnection(strConn); conn.Open(); if (TransactionRequired == true) { tran = conn.BeginTransaction(); } else { tran = null; } } catch (Exception ex) { throw ex; } } public DataSet Retrieve(string ProcedureName, SqlParameter[] ParamCollection) { try { DataSet ds = new DataSet();SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; if (ParamCollection != null) SetParameters(cmd, ParamCollection); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = ProcedureName; SqlDataAdapter adp = new SqlDataAdapter(cmd); adp.Fill(ds); return ds; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } public void SetParameters(SqlCommand cmd, SqlParameter[] ParamCollection) { try { foreach (SqlParameter param in ParamCollection) cmd.Parameters.Add(param); } catch (Exception ex) { throw ex; } } ---------------------------------------------------------------------------------------------------------------------- Thnking you all in advance.
Thanks for your reply. Your answers could have solved my problem. If I limit the no. of users. But that is the problem. No. of users will increase in coming time. So if there is any other efficient way to solve this problem. Plz tell me
-
Thanks for your reply. Your answers could have solved my problem. If I limit the no. of users. But that is the problem. No. of users will increase in coming time. So if there is any other efficient way to solve this problem. Plz tell me
Neither one of us suggested you needed to limit the number of user being returned. Both of us suggested that if you increase the timeout property of your command object then they will all return. If you look at the time in your query tool that it take to execute and it is like 1 minute, then increase your command timeout to like 2 minutes and you should be fine. If the time in it takes to execute is like 5 minutes then fix your query because you are missing indexes or something if it is taking that long to execute. Ben