Thanks,
Bill

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

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

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.

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.

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:

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

You could try putting:

Option (Recompile)

as the last line in your proc. This will cause SQL to recalculate its execution plan each time the proc runs. While this, in itself is an overhead, it is better than using an old and entirely inappropriate plan if you have a wide range of options that SQL should be considering on each execution although SQL is supposed to be pretty smart in knowing when to recalculate an execution plan.

Bottom line is that 90 seconds is a looooong time and the issue is probably in the area of your proc using full table scans rather than index seeks. Just in case you don't know - the best way to sort these issues is to put the proc's code is Query Analyzer and have a look at the Execution Plan

Cheers, Peter

Just looked at the code in your post and the line:

cmd.Parameters.AddWithValue("@invdate", InvDateDE.DateTime.ToShortDateString()).SqlDbType = SqlDbType.VarChar;

may need attention IF InvDate is an indexed column in your database. If it is then the parameter must a Date data type not VarChar.

If you need to pass in a VarChar data type then your proc needs to convert the value to a date data type before it is used in a Where clause. If this isn't done you will end up with a full table scan rather than a Seek on your InvDate index.

Cheers, Peter

-Larry

Let me propose another avenue that is much more beneficial to you and the end-user when waiting for large VarBinary(MAX) fields.  StrataFrame has two push methods and two pull methods for this very purpose called: PushBlobField, PushBlobFieldAsync, PullBlobField, and PullBlobFieldAsync.

Whether you choose to use the async method or block and use the non-async methods, this will never timeout and will stream as big of a file that you will ever use.  If you use the async method (which I recommend) you can also add handlers to show a progress bar of the save and/or retrieval.

Push Async Example

The pull will work very much like the push.  This is a great method and is what we do when updating large binary data fields....that is how this made it into the framework

When using the Push/Pull methods should the VarBinary fields be included in the BO FieldsToExcludeFromInsert and FieldsToExcludeFromUpdate properties?

I cannot find any documentation for the push/pull BLOB functions you referenced. Could you please give us a sample or point me to where it is used.

BO Method

In this example, the above field name is "FieldName" so we would add "FieldName" to the FieldsToExcludeFromUpdate and FieldsToExcludeFromInsert.  This way the PK, time stamp columns, etc. would save before we try to save our blob data.  So taking this example, and assuming that you have added the "FieldName" to the FieldsToExclude properties on the BO, the code would look something like this:

That should give you a good starting point.

We have this working well now, thanks for the help

Kenneth langley