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,
>
>

Hi,

you create SqlCommand and then SqlDataAdapter, but they have got no
asociation except query string. So Timeout is set to cmd, but not to command
da creates when you pass him the query string. You have to create
SqlDataAdapter like this:

SqlDataAdapter da = new SqlDataAdapter(cmd);

Now da uses cmd and it should work.

HTH
Pepa

"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,
>
>