I do like interesting problems and like them even better if I can solve them!   This one was a bit different so thought I would run through it in this article in case it helps anyone else out there.

In this case the customer was reporting that the Bank Statement Import process was completing with no error messages and the BSIIMP table is populated accordingly, however no record was written into the RELBANK table.   Even more strange is that they have two folders on the same X3 solution and it works OK in one, but fails in the other.

The first step was to check for any differences between working and non-working folders using technique from "How to find what I changed in a folder (aka what did I break this time)" but this didn't find any relevant differences.

We then ran an openlog engine trace on both the working and non-working folders using level 2 but these traces looked identical, so we then added debug (level 514)   This also seemed to be virtually identical however did throw up one very subtle but interesting difference at the point where the record "write" was being made.   

NON WORKING
<channel 2>@X3.TRT/BSILIBRCL$adx(452)                    |  |  |  |  |  |  |  |  |  |  | Write  
<channel 514>~W=1
<channel 512>wr_enr(keycl:21,flg:0) version:16
<channel 514>~W=0
<channel 2>@X3.TRT/BSILIBRCL$adx(453)                    |  |  |  |  |  |  |  |  |  |  | Return

WORKING
<channel 2>@X3.TRT/BSILIBRCL$adx(452)                    |  |  |  |  |  |  |  |  |  |  | Write
<channel 514>~W=1
<channel 512>wr_enr(keycl:21,flg:0) version:16
<channel 2>@X3.TRT/BSILIBRCL$adx(453)                    |  |  |  |  |  |  |  |  |  |  | Return

I couldn't immediately work out why the extra "~W=0" was in the trace and the online help for the "write" function was not very forthcoming, so ended up doing another engine trace with level 527 logging.  This level includes the detailed trace from the adonix process, but additionally provides tracing of the database process (sadoss /sadora)    Now things started to make sense, as in the database process trace log file we can see some errors!

<channel 8>resultat execution immediate :-1
<channel 8>resultat catch error : 0 avec no 2601 et state :23000 
<channel 8>Classic Error ERDUPCL with Error No 2601

Microsoft SQL Server error 2601 is "Cannot insert duplicate key row", so this is the root cause for the failing write into the database table.

SIDE NOTE: to confirm the SQL server error meaning, you can run the following SQL

SELECT message_id AS Error,
    severity AS Severity,
    [Event Logged] = CASE is_event_logged
        WHEN 0 THEN 'No' ELSE 'Yes'
        END,
    [text] AS [Description]
FROM sys.messages
WHERE language_id = 1033  // US English
    and message_id = 2601  // Enter the error message you want to check here
ORDER BY message_id;

It is disappinting of course that the Bank Import didn't report this back as an error to the user, but at least now we can do more meaningful research. 

Looking at the RELBANK table definition (https://online-help.sagex3.com/erp/12/en-us/Content/MCD/RELBANK.htm) we can see there are four indexes which do not allow duplicates.  It turns out there are a huge number of records in this table for the customer, and the folder had been previously migrated, so most likely the NUMRBK or RBKNUM sequence numbers need to be addressed, although should also check for any data corruption.

I hope this problem run through was an interesting read.  If you have any comments then feel free to leave them below.