I have a query that is taking about 90 seconds.  The window times out before any data is displayed.  How do I increase the connection timeout for this query?

Thanks,
Bill

Perhaps I should have title this thread: Increase Query Timeout

Here is the error:

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

My query has been timed at about 90 seconds.  What can I do to get StrataFrame to increase this timeout?

Thanks!!

Hi Bill,

I found a thread that may give you an idea about using a separate thread for your query.  Check it here: http://forum.strataframe.net/FindPost11979.aspx

Edhy Rijo

Bill,

You can set the remote query timeout on the server, I had to change this when I was doing some import operations from my old foxpro data to Sql. I'm not sure if there is a way to change it ona per query basis but this did work for me with the same error.

Bill,

I thought that I had found where to set it on a per query basis, I set it server wide to deal with an issue with E-connect for Great Plains, to set in on a per query basis simply set the commandtimeout property of the Sql Command.

Dim loCommand As New SqlCommand()

loCommand.CommandTimeout = 150

'-- Build the Query

loCommand.CommandText = "SELECT * FROM WHATEVER"

Yeah, Paul nailed it on the last post.  The most common timeout that is overlooked is on the command itself.  You can set the connection timeout until you are blue in the face...but if you don't set the command time out it can still timeout on you.  So if you set the command timeout you should be good to go!

Thanks Paul and Edhy!!

I am still using SQL Server 2000...I will see if I can find a corresponding configuration for setting the timeout.

Here is my code that I use for retrieving the data:

String mPartNum = String.Empty;
if (PartNumCBE.SelectedValue != null)
{
    mPartNum = (String)PartNumCBE.SelectedValue;
}
SqlParameter mItemCode = new SqlParameter("@partnum", mPartNum);
ADUserBO mADUser = new ADUserBO();
SqlParameter mDiv = new SqlParameter("@div", mADUser.LocationIndex);
SqlParameter mInvDate = new SqlParameter("@invdate", InvDateDE.DateTime.ToShortDateString());
rawMaterialValuationBO1.FillByStoredProcedure("spx_GetRunningInventory_FinishedGoods", mItemCode, mDiv, mInvDate);
gridView1.Columns["CurrQty"].FilterInfo = new DevExpress.XtraGrid.Columns.ColumnFilterInfo("CurrQty > 0");
gridView1.ApplyColumnsFilter();

If the command timeout can be set, how do I do that from the BO?

Thanks,
Bill

Through a command:

Thanks.  Took me a minute to make the right connection from the command to the BO.  Here is my revised code:

ADUserBO mADUser = new ADUserBO();
String mPartNum = String.Empty;
if (PartNumCBE.SelectedValue != null)
{
    mPartNum = (String)PartNumCBE.SelectedValue;
}
SqlCommand cmd = new SqlCommand("spx_GetRunningInventory_FinishedGoods");
cmd.CommandTimeout = 0;
cmd.Parameters.AddWithValue("@partnum", mPartNum).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("@div", mADUser.LocationIndex).SqlDbType = SqlDbType.Int;
cmd.Parameters.AddWithValue("@invdate", InvDateDE.DateTime.ToShortDateString()).SqlDbType = SqlDbType.VarChar;
cmd.CommandType = CommandType.StoredProcedure;
rawMaterialValuationBO1.FillDataTable(cmd);
gridView1.Columns["CurrQty"].FilterInfo = new DevExpress.XtraGrid.Columns.ColumnFilterInfo("CurrQty > 0");
gridView1.ApplyColumnsFilter();

Works great!  Thanks a bunch.  Oh...and the wait window...awefully nice feature!!

Now, I just need to work on getting that sproc to run faster than 90 seconds.  It has several nested CASE statements that I think are gumming up the works.  Not too sure how to simplify those conversions that are going on.

Happy Friday!
Bill