I am executing a stored procedure and returning a resultset.  The
stored procedure takes approx 40 secs to execute.  I have set the
connection and command timeout to 10000, but I am still getting the
error, "Timeout expired.  The timeout period elapsed prior to
completion of the operation or the server is not responding."

Here is the code for executing the stored procedure.
private DataSet ExeCmdWithDataSet(string SQLText, string strdbconnect)
{
                SqlConnection cn = new SqlConnection();
                cn.ConnectionString = strdbconnect;
                cn.Open();

                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = 10000;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQLText;
                cmd.Connection = cn;

                cmd.Prepare();

                SqlDataAdapter da = new SqlDataAdapter(SQLText, cn);
                da.Fill(ds);
                cn.Close();

                return ds;
}

Please help.

Thanks,

Mmm...

  I think it could be a good idea to check first if the problems is related 
with the DataSet or if it's the Stored procedure or if it's too much data.

  Could try to run the same query using a SqlDataReader and a loop and check 
if using that retrieves data ? (from there we can further research and check 
if it's because of the dataset or ...)

  HTH 
    Braulio

/// ------------------------------
/// Braulio Diez
///
/// http://www.tipsdotnet.com
/// ------------------------------




"Manohar" wrote:


> I am executing a stored procedure and returning a resultset.  The
> stored procedure takes approx 40 secs to execute.  I have set the
> connection and command timeout to 10000, but I am still getting the
> error, "Timeout expired.  The timeout period elapsed prior to
> completion of the operation or the server is not responding."
> 
> Here is the code for executing the stored procedure.
> private DataSet ExeCmdWithDataSet(string SQLText, string strdbconnect)
> {
>                 SqlConnection cn = new SqlConnection();
>                 cn.ConnectionString = strdbconnect;
>                 cn.Open();
> 
>                 DataSet ds = new DataSet();
>                 SqlCommand cmd = new SqlCommand();
>                 cmd.CommandTimeout = 10000;
>                 cmd.CommandType = CommandType.Text;
>                 cmd.CommandText = SQLText;
>                 cmd.Connection = cn;
> 
>                 cmd.Prepare();
> 
>                 SqlDataAdapter da = new SqlDataAdapter(SQLText, cn);
>                 da.Fill(ds);
>                 cn.Close();
> 
>                 return ds;
> }
> 
> Please help.
> 
> Thanks,
> 
>