HQ Client and HQ Sync Error

Posted by Every1Tech on July 10, 2008, 1:14 am

Please log in for more thread options

I get an error everytime I sync the Branch Server with the HQ Server?

<<ExecuteCommand>> Error - 2147217871: Timeout expired
UPDATE ItemDynamic
SET QuantityCommited = ItemDynamic.SnapShotQuantityCommitted
FROM #Temp JOIN ItemDynamic
ON #Temp.ItemID = ItemDynamic.ItemID
AND ItemDyna

This is the error message I can see on the HQ Client.

Can someone help me out?

Thanks

Posted by YidMo on July 14, 2008, 7:16 pm

Please log in for more thread options

Every1Tech;

I'm guessing you're about to pull your hair out on this one. I've
encountered it many a time, and have a working "fix" for it, however it's a
bit detailed and delicate. First off, let me explain what's causing this:

What's happening is, HQClient is sending a bunch of information to HQServer,
and then holding the connection open while it waits for a confirmation from
HQServer saying that the data's been processed successfully. SQL doesn't
actually have timeouts, however applications do, and HQClient's timeout is
set to guestimate. HQServer is getting overloaded with whatever information
HQClient is sending (multiple possibilities for this, keep reading) and isn't
coming back with it's acknowledgment within that time-frame. When HQClient
disconnects and then goes for it's next connection, the same thing is
happening over and over again.

The cause of the timeout can be from a few things, however most usually it's
one of two things in specific; either your HQServer machine is running really
slowly and simply taking ages to do the simplest of things, or more likely,
something's happened to the store database and HQClient is sending way too
much information for HQServer to handle. Assuming the former, beef-up the
server; assuming the latter, keep reading.

First, you need to think back to if you've modified (or deleted?) lots of
anything lately, even inadvertently. I'm never thrilled to admit my own
stupidity, however accidents (and self-inflicted dire consequences) are how I
learn best, so I'll admit here that when this has happened in the past to me,
it was always my fault in some way, and I just miscalculated the outcome.
Second, you need to go to the HQClient of the machine giving you the error
and go into it's HQClient config, enable the debug logging, and then try a
connection. When this connection fails, scroll back and check what data was
being prepared or passed, likely it's going to list one (or possibly more)
tables with a HUGE number of records, more than usual (my rule of thumb is
that id it's over 1000 records, it's possibly the problem, and if it's over
5000 records, it IS the problem).

Once you know what table it is, here comes the really technical and tricky
parts. There's a dbTimeStamp in each table, which is what the system uses to
detect modified/new records. a dbTimeStamp isn't a date/time field, it's a
system-counter, sort of like a database-wide index field. There aren't any
repeats in the system, and any modification or new record increments by one,
no matter what. When too many records were modified on the store level, this
field was updated too as is always the case, and that's seen as a change that
needs to be sent to HQServer. What you essentially need to do it reset that
field for the records that don't REALLY need to go to HQServer, or if they
really do, you need to reset them all to zero, and then set them to new
values in smaller groups so as to not overwhelm HQServer's SQL.

The problem is, you can't modify this field, ever. The workaround involves
creating a duplicate table with a zerod-out field which is later converted to
a dbtimestamp, thus making all the records in it have a dbtimestamp earlier
than HQClient (or, more appropriately, the WS401) is looking for, and making
that copy in effect replace the existing table. I've got a process down for
this, however for that you're going to need to contact me directly if
interested.

YidMo

"Every1Tech" wrote:

> I get an error everytime I sync the Branch Server with the HQ Server?

> <<ExecuteCommand>> Error - 2147217871: Timeout expired

> UPDATE ItemDynamic

> SET QuantityCommited = ItemDynamic.SnapShotQuantityCommitted

> FROM #Temp JOIN ItemDynamic

> ON #Temp.ItemID = ItemDynamic.ItemID

> AND ItemDyna

> This is the error message I can see on the HQ Client.

> Can someone help me out?

> Thanks

Posted by Paco on July 26, 2008, 6:31 pm

Please log in for more thread options

I´m interested

"YidMo" wrote:

> Every1Tech;

> I'm guessing you're about to pull your hair out on this one. I've

> encountered it many a time, and have a working "fix" for it, however it's a

> bit detailed and delicate. First off, let me explain what's causing this:

> What's happening is, HQClient is sending a bunch of information to HQServer,

> and then holding the connection open while it waits for a confirmation from

> HQServer saying that the data's been processed successfully. SQL doesn't

> actually have timeouts, however applications do, and HQClient's timeout is

> set to guestimate. HQServer is getting overloaded with whatever information

> HQClient is sending (multiple possibilities for this, keep reading) and isn't

> coming back with it's acknowledgment within that time-frame. When HQClient

> disconnects and then goes for it's next connection, the same thing is

> happening over and over again.

> The cause of the timeout can be from a few things, however most usually it's

> one of two things in specific; either your HQServer machine is running really

> slowly and simply taking ages to do the simplest of things, or more likely,

> something's happened to the store database and HQClient is sending way too

> much information for HQServer to handle. Assuming the former, beef-up the

> server; assuming the latter, keep reading.

> First, you need to think back to if you've modified (or deleted?) lots of

> anything lately, even inadvertently. I'm never thrilled to admit my own

> stupidity, however accidents (and self-inflicted dire consequences) are how I

> learn best, so I'll admit here that when this has happened in the past to me,

> it was always my fault in some way, and I just miscalculated the outcome.

> Second, you need to go to the HQClient of the machine giving you the error

> and go into it's HQClient config, enable the debug logging, and then try a

> connection. When this connection fails, scroll back and check what data was

> being prepared or passed, likely it's going to list one (or possibly more)

> tables with a HUGE number of records, more than usual (my rule of thumb is

> that id it's over 1000 records, it's possibly the problem, and if it's over

> 5000 records, it IS the problem).

> Once you know what table it is, here comes the really technical and tricky

> parts. There's a dbTimeStamp in each table, which is what the system uses to

> detect modified/new records. a dbTimeStamp isn't a date/time field, it's a

> system-counter, sort of like a database-wide index field. There aren't any

> repeats in the system, and any modification or new record increments by one,

> no matter what. When too many records were modified on the store level, this

> field was updated too as is always the case, and that's seen as a change that

> needs to be sent to HQServer. What you essentially need to do it reset that

> field for the records that don't REALLY need to go to HQServer, or if they

> really do, you need to reset them all to zero, and then set them to new

> values in smaller groups so as to not overwhelm HQServer's SQL.

> The problem is, you can't modify this field, ever. The workaround involves

> creating a duplicate table with a zerod-out field which is later converted to

> a dbtimestamp, thus making all the records in it have a dbtimestamp earlier

> than HQClient (or, more appropriately, the WS401) is looking for, and making

> that copy in effect replace the existing table. I've got a process down for

> this, however for that you're going to need to contact me directly if

> interested.

> YidMo

> "Every1Tech" wrote:

> > I get an error everytime I sync the Branch Server with the HQ Server?

> >

> > <<ExecuteCommand>> Error - 2147217871: Timeout expired

> > UPDATE ItemDynamic

> > SET QuantityCommited = ItemDynamic.SnapShotQuantityCommitted

> > FROM #Temp JOIN ItemDynamic

> > ON #Temp.ItemID = ItemDynamic.ItemID

> > AND ItemDyna

> >

> > This is the error message I can see on the HQ Client.

> >

> > Can someone help me out?

> >

> > Thanks