Timeout with a Stored Procedure

[iebidan]

Hi guys, hey I've been out of the forums for a long time, also out of programming, but now we've a problem and prolly you know the answer to this

 

I've a stored procedure that takes too long to execute, it processes large amounts of information (up to 750 million rows) but when we call it (using the following code) we get a timeout, how can I make my application wait til the procedure is done? also this stored procedure calls 11 user defined functions. If I execute the procedure using Query Analyzer it runs perfectly and I never get a timeout. How can I solve this?

 

string sDate = "20041130"; string sLastDate ="20041031";
string sConnect = "server=--;UID=--;PWD=--;DATABASE=--;";
SqlConnection Conn = new SqlConnection(sConnect);
SqlCommand ObjCmd = new SqlCommand("sp_inv",Conn);
ObjCmd.CommandType = CommandType.StoredProcedure;
SqlParameter DateParam = new SqlParameter("@DATE",sDate);
ObjCmd.Parameters.Add(DateParam);
SqlParameter LastDateParam = new SqlParameter("@LASTDATE",sLastDate);
ObjCmd.Parameters.Add(LastDateParam);
try
{
            Conn.Open();
ObjCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
Conn.Close();
Conn.Dispose();
}

 

Thanks in advance

[Derek Stone]

Set the SqlCommand.CommandTimeout property.

[iebidan]

Yeah, I did, I found out this 10 minutes after posting this, thanks anyway, sorry for the delay on posting the solution, I was trying to set the timeout in the connection string.

Sometimes my brains seems to work in a very slow way