Weird, I haven't seen this before.

Do the Status and PostStatus fields in tciBatchLog look normal?

Hey Tim..  Yes tcibatchLog shows it posted successfully.   Sometimes the problem is it doesn't update the balance of invoices and other times it is just plain missing final records in cash management such as the one I just worked on.  

As I mentioned it is very rare when the issue does occur, but when it does it takes a data fix to correct all the records impacted.   It's almost acts if an error occurred somewhere in the posting process and Sage 500 still continues on without rolling back and reporting the error.

Lou - I take it you have not been able to capture it before it happens...  I would go for a point in time restore to just before they posted the batch and repost and see if it happens.  I've run into similar stuff once or twice before and was never able to track it down but if it's happening often enough then maybe it's worth the time.

Hey John,

   Nope, I have not been able to capture it before it happens.  Usually, a customer doesn't realize this has occurred until they do something else within 500 that indicates to them there is a problem (i.e. Invoices still showing balances).  By then maybe one or two backups have occurred, so the chance of getting back to a time before the problem occurred is slim.  Also, there is no guarantee the issue will occur.  As I said it is very random and once you fix the first instance of it, it doesn't appears to happen again.  At least with the customers I've already assisted with the issue.

   I will say this is the first time I've seen the issue not creating all the cash management transactions other than the record in tcmCashRecptDetl.  Usually when this issue occurs, it doesn't update the invoices balances.  Customers then try to create another cash receipt for them and find they cannot select the invoices as there is already records in tcmCashRcptDetl.  

Well if I was significantly compensated then I would put a trigger on the parent tables and have it notify me, log or something.  It does have a whiff of a SQL bug though, are they on support/newer versions?

Well Good luck with that John.  I have seen this rare issue on various versions of Sage 500 and various operating systems and SQL Servers.   No two systems where this issue has occurred is the same.  It is one of the weirdest things I've seen with 500 in all my years working with it.  

The only thing I can see to alleviate the problem is to rewrite the posting code to have all the updates, inserts, and deletes for the Cash Receipts in one transaction instead of spread out over the process.  That way if one thing fails then the entire transaction will rollback.   I would also wrap this transaction in a Try Catch Block and use the routine to log any error to tciSQLExceptionLog.for further troubleshooting

Lou,

This is not SQL. It is either an unhandled error in the code or possibly an unhandled timeout. We have seen this and it may have been corrected. I am checking with our internal troops.

Have you contacted support?

This is not the fix but I have added the following indexes in almost all of our installs and have convinced Sage to add them to the base in an upcoming PU or v2015.

/*

Suggested Indexes on base Sage 500 ERP Tables

12/16/2013

*/

CREATE NONCLUSTERED INDEX [IX_ApplyToInvcKey_RKL]

ON [dbo].[tarCustPmtAppl]

([ApplyToInvcKey]);

GO

CREATE NONCLUSTERED INDEX [IX_ApplyToPmtKey_RKL]

ON [dbo].[tarCustPmtAppl]

([ApplyToPmtKey]);

GO

CREATE NONCLUSTERED INDEX [IX_ApplyToInvcKey_RKL]

ON [dbo].[tarPendCustPmtAppl]

([ApplyToInvcKey]);

GO

CREATE NONCLUSTERED INDEX [IX_ApplyFromPmtKey_RKL]

ON [dbo].[tarPendCustPmtAppl]

([ApplyFromPmtKey], [ApplyToInvcKey]);

GO

CREATE NONCLUSTERED INDEX [IX_RevrsCustPmtKey_RKL]

ON [dbo].[tarCustPmt]

([RevrsCustPmtKey]);

GO

CREATE NONCLUSTERED INDEX [IX_Param2CustCode_RKL]

ON [dbo].[tccTran]

(

[Param2CustCode], [RespReferenceID]

);

GO

Hey Joe,

    Thanks for the indexes.   I was thinking that it was a timeout issue as well, but have not had any of the customers who ran into this issue confirm that they had a timeout message or ended the task while it was in the midst of posting.  

     I haven't reported it to Sage 500 support as they are pretty clear on the fact if they can't reproduce it then it can't be submitted to engineering.   As I mentioned in a previous post, if you look at how AR Cash Receipts are posted, records are updated, insert and deleted at various points.  However, there is no logic to verify for example when the record in tarBatch  which contains the cash account key is deleted that the corresponding record in tcmCashTran was created previously.  That's why I recommended that the posting process be re-engineered a bit so all these inserts updates and deletions occur together within a small transaction so if one fails everything rolls back.

Darn, I thought maybe Sage had contracted out to you to fix it.  :(

Nice one John. Good idea as well.