|
.net
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SqlClient.SqlException: Timeout expired.When the query returns thousands of lines of data (in Query Analyzer it can take 2 minutes) I receive the following error: Message: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. My assumption is that I need to raise the command timeout to correct this. I cannot find a way while using SqlHelper.ExecuteDataSet to include a change to the command timeout. Here is an example of my webservice: [WebMethod] public System.Data.DataSet QuerySpecificTransaction(int transactionID) { SqlConnection conn = new SqlConnection(); DataSet ds = new DataSet(); SqlParameter[] parms = new SqlParameter[1]; try { conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; parms[0] = new SqlParameter("@transactionID", SqlDbType.Int); parms[0].Value = transactionID; ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "QuerySpecificTransaction", parms); } catch(Exception exc) { throw exc; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } return ds; } you are correct, you need set the Timeout property of the sqlcommand object.
you will proably have to add code to your sqlhelper object to support this. -- bruce (sqlwork.com) Show quote "Matt" <M***@discussions.microsoft.com> wrote in message news:3F60BBBE-3A31-4DA3-BB51-117291CAD150@microsoft.com... > I'm having an issue with returning a large amount of data into a dataset. > When the query returns thousands of lines of data (in Query Analyzer it > can > take 2 minutes) I receive the following error: > Message: System.Web.Services.Protocols.SoapException: Server was unable to > process request. ---> System.Data.SqlClient.SqlException: Timeout expired. > The timeout period elapsed prior to completion of the operation or the > server > is not responding. > > My assumption is that I need to raise the command timeout to correct this. > I cannot find a way while using SqlHelper.ExecuteDataSet to include a > change > to the command timeout. Here is an example of my webservice: > > > [WebMethod] > public System.Data.DataSet QuerySpecificTransaction(int transactionID) > { > SqlConnection conn = new SqlConnection(); > DataSet ds = new DataSet(); > SqlParameter[] parms = new SqlParameter[1]; > > try > { > conn.ConnectionString = > System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; > parms[0] = new SqlParameter("@transactionID", SqlDbType.Int); > parms[0].Value = transactionID; > ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, > "QuerySpecificTransaction", parms); > } > catch(Exception exc) > { > throw exc; > } > finally > { > if (conn.State != ConnectionState.Closed) > { > conn.Close(); > } > } > return ds; > } Thanks, Bruce! I've been digging through the SqlHelper object, but can't
find how to accomplish this. Do you happen to know a good source? Matt Show quote "Bruce Barker" wrote: > you are correct, you need set the Timeout property of the sqlcommand object. > you will proably have to add code to your sqlhelper object to support this. > > -- bruce (sqlwork.com) > > > "Matt" <M***@discussions.microsoft.com> wrote in message > news:3F60BBBE-3A31-4DA3-BB51-117291CAD150@microsoft.com... > > I'm having an issue with returning a large amount of data into a dataset. > > When the query returns thousands of lines of data (in Query Analyzer it > > can > > take 2 minutes) I receive the following error: > > Message: System.Web.Services.Protocols.SoapException: Server was unable to > > process request. ---> System.Data.SqlClient.SqlException: Timeout expired. > > The timeout period elapsed prior to completion of the operation or the > > server > > is not responding. > > > > My assumption is that I need to raise the command timeout to correct this. > > I cannot find a way while using SqlHelper.ExecuteDataSet to include a > > change > > to the command timeout. Here is an example of my webservice: > > > > > > [WebMethod] > > public System.Data.DataSet QuerySpecificTransaction(int transactionID) > > { > > SqlConnection conn = new SqlConnection(); > > DataSet ds = new DataSet(); > > SqlParameter[] parms = new SqlParameter[1]; > > > > try > > { > > conn.ConnectionString = > > System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; > > parms[0] = new SqlParameter("@transactionID", SqlDbType.Int); > > parms[0].Value = transactionID; > > ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, > > "QuerySpecificTransaction", parms); > > } > > catch(Exception exc) > > { > > throw exc; > > } > > finally > > { > > if (conn.State != ConnectionState.Closed) > > { > > conn.Close(); > > } > > } > > return ds; > > } > > > Here's the fix in case anyone else has this issue.
******************************************************** [WebMethod] public System.Data.DataSet QuerySpecificTransaction(int transactionID) { SqlConnection conn = new SqlConnection(); SqlParameter[] parms = new SqlParameter[1]; try { conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; parms[0] = new SqlParameter("@transactionID", SqlDbType.Int); parms[0].Value = transactionID; SqlCommand com = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(com, conn, null, CommandType.StoredProcedure, "QuerySpecificTransaction", parms, out mustCloseConnection); using(SqlDataAdapter da = new SqlDataAdapter(com)) { DataSet ds = new DataSet(); da.Fill(ds); com.Parameters.Clear(); if(mustCloseConnection) conn.Close(); return ds; } } catch(Exception exc) { throw exc; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms, out bool mustCloseConnection) { if (conn.State != ConnectionState.Open) { mustCloseConnection = true; conn.Open(); } else { mustCloseConnection = false; } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; cmd.CommandTimeout = 240; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } return; } ******************************************************** Show quote "Matt" wrote: > I'm having an issue with returning a large amount of data into a dataset. > When the query returns thousands of lines of data (in Query Analyzer it can > take 2 minutes) I receive the following error: > Message: System.Web.Services.Protocols.SoapException: Server was unable to > process request. ---> System.Data.SqlClient.SqlException: Timeout expired. > The timeout period elapsed prior to completion of the operation or the server > is not responding. > > My assumption is that I need to raise the command timeout to correct this. > I cannot find a way while using SqlHelper.ExecuteDataSet to include a change > to the command timeout. Here is an example of my webservice: > > > [WebMethod] > public System.Data.DataSet QuerySpecificTransaction(int transactionID) > { > SqlConnection conn = new SqlConnection(); > DataSet ds = new DataSet(); > SqlParameter[] parms = new SqlParameter[1]; > > try > { > conn.ConnectionString = > System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]; > parms[0] = new SqlParameter("@transactionID", SqlDbType.Int); > parms[0].Value = transactionID; > ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, > "QuerySpecificTransaction", parms); > } > catch(Exception exc) > { > throw exc; > } > finally > { > if (conn.State != ConnectionState.Closed) > { > conn.Close(); > } > } > return ds; > } |
|||||||||||||||||||||||