Failed eConnect document leaves orphaned records

I assisted on an eConnect incident today that was quite interesting and I thought I'd share the case and the solution.

The issue:

The ISV was integrating a Payables Invoice using eConnect 10.0.

The XML document had 2 distributions and the invoice data.

What the ISV found was that there was an issue in the "header" portion of the document and eConnect was giving the appropriate error for that mistake.

What was unusual in this case was that he found the distributions were still in the PM10100 after the error!!!!

The ISV submitted the XML file in a case using Fabrikam data so we could try it here.

Using the Direct Document Sender against Fabrikam on GP 2010 (it was handy) we couldn't reproduce the issue.

Well, that was 2010 and the ISV used 10.0 - that must be the issue and why we couldn't repro.

So trying the Direct Doc Sender with the XML on 10.0 to repro, nope still couldn't as the distributions were properly rolled back.

During the test, we did a SQL trace while running the integraton.

TRANSACTION START

The call to the proc for the taPMDistributionCreate runs twice - once for each distribution inserted.

Next the taPMTransactionInsert to create the header record

Because of the error in the header (it doesn't matter what the error is), the taUpdateString proc is called.

Lastly, the TRANSACTION ROLLBACK happens due to the error

So looks like what we'd expect and of course querying the PM10100 does not have the orphaned distributions.

Because we couldn't repro the issue with the XML, that would suggest the issue is either environment or with the application.

As a coding issue is easier to troubleshoot, we got the app from the ISV to test here.

The ISV app was straightforward (perhaps a test app he was using?) and easy to run.  It essentially was a rewrite of the Direct Document Sender app and allowed the user to pick the XML file and then send it to eConnect - nothing fancy.

So it should work right?

Well, no it didn't.  We got the same result as the ISV did - eConnect throws the exception on the document failure but the distributions are left in the table and not rolled back as part of the transaction.

What the heck?

Looking over the app, there really wasn't a lot of code for a bug to hide.

Push Button to import a text file.

Read throught it and put into a text field.

Set the connection string.

Send to the eConnect EntryPoint_Main() function.

Done.

What could be simpler and what could possibly be the issue?

Running a SQL trace looked similar as the trace from the Direct Doc Sender except for the beginning & ending lines.  There was no TRANSACTION START or TRANSACTION ROLLBACK commands.

Puzzling - and also the reason to why we were getting the orphaned distributions.

If there isn't a transaction rollback happening, then the 'bad' data inserted into the distribution table isn't rolled back with the failure.  There isn't bad data inserted to the PM10000 table because with the error the insert never happened and so didn't need to roll back that piece.

So that explains that - but why?

Looking at the connection string more closely, I noted a property I've never used before - something that I'd never seen before honestly.

This extra property was

enlist=false

Enlist?  What is that?  Such as "Enlist into a SQL transaction = false?"

Sounded plausible to me.  We took that out and re-ran the test with the ISV app.

Bingo - worked as expected.  The SQL trace now did show the transaction start and rollback and the distributions were no longer in the table as desired.

I did a little research on this and this MSDN article talks about the property.  Actually that isn't quite right and I can't find the exact article on MSDN that IS the right one.  But this one does a good job of explaining the behavior.

So just a word of warning out there - you don't want to use this property in your eConnect integrations.

Best Regards,

Patrick Roth
Senior Escalation Engineer
GP Developer Support