G
Guest
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;
}
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;
}