Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gratisaccount
Starting Member
2 Posts |
 Posted - 2007-10-22 : 03:38:01
|
| Hi All,I have written stored procedure which calculates points for around 1000 users (in future this no. will increase).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; }}Thanking you all in advance. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-22 : 04:36:27
|
| "while running from the sql server itself it works fine."How long does it take?How much data does it return?Kristen |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
gratisaccount
Starting Member
2 Posts |
Posted - 2007-11-02 : 02:45:48
|
| thank you both for your reply.I actually don't wanted to increase the connection timeout. Because it will not be permanent solution(I think so).So I tried to implement the asynchronous call.for this I wrote the following code in my helper file which is responsible for all database connection. public int AsynExecute() { try { SqlCommand cmd = new SqlCommand(); int result = 0; cmd.Connection = conn; //cmd.CommandTimeout = 90000; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "MyProcedure"; IAsyncResult myAsyn = cmd.BeginExecuteNonQuery(); myAsyn.AsyncWaitHandle.WaitOne(-1,true); result = cmd.EndExecuteNonQuery(myAsyn); return result; } catch (Exception ex) { conn.Close(); throw ex; } }This code generates the error of "connection timeout".but I uncomment the commandtimeout it works fine.Please help me why this happening.I have even set the "Async = true" in both the connection string & in the page where this method is being called. |
|
|
|
|
|
|
Timeout Problem