SqlClient.SqlException: Timeout expired.

[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;
}

 

[Bruce Barker]

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)

 

[Guest]

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

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)

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

 

[Guest]

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

********************************************************

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