I have a feeling I am in danger of stepping on something soft and smelly. Before I take another step, I have decided to seek some advice from some SQL Sages. First, some background.
I have a process which requires transactions from two different tables to be matched and the matching to be recorded in a third. Specifically, I am developing a Bank Reconciliation app and I need to match transactions from the General Ledger of our JDE financial system against transactions imported from the Bank.
Thus, I have separate Bank and GL transactions tables, along with a matches table which records the matching association. My user interface provides a matching function which is linked to a "MatchPending" table. A user selects records from both sides to match, with each selected transaction being added into the Pending table. If the matching is saved, the GL and Bank transactions are updated to record them as matched, and the matching details are inserted in to the matches table. Pending records can be specifically removed by the user, and any records that remain when the form is closed, will be deleted from the table.
The problem I am having relates to the Pending table and multiple users - occasionally, when several users are matching at the same time, a non-specific database error will be returned by the user interface. If there is only one user on the system, the error does not occur. Records in the Pending table are suitably structured to ensure that each user only alters their own records so I'm not suffering from record locking or contention issues. Records have a Bank Account Number and User ID and only one user will be operating on a single account.
The error is popping up when a user is attempting to add a new record into the Pending table and I am beginning to suspect that the method I am using may not be the most robust, or that the table requires a different maintenance method given the transient nature of the data. One of the reasons for these conclusions is elimination, the other is the fact that I can no longer force the error when I use the test system. I'll explain.
On Friday, from my test system, I could generate the error. Today, Monday, I can't. On Friday, I removed two indexes (FinancialYear and FinancialPeriod) from the Pending table as they were not relevant. I also ran sp_updatestats on that database. Over the weekend, a maintenance plan performed an index rebuild. I have asked the users to re-test the interface to see if they no longer receive the errors as well, but I won't hear back until later today, early tomorrow.
I can see several alternative paths to follow, but this leads me back to my fear of stepping on something soft and squishy. If anyone has been in the same or similar position with a table and data of this nature, I would be happy to hear what you have to say.
My thanks in advance.