I assisted on an eConnect incident today that was quite interesting and I thought I'd share the case and the solution.
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.
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.
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? 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.
Patrick RothSenior Escalation EngineerGP Developer Support
I wonder if this could be the cause of my eConnect issues. Every now and then eConnect errors out and tells me that it can not save a null record to the temp table?? Most of my errors happen on the customer table. I am not sure on the exact error message, but I do know if I keep SQL Server Management Tool open, the errors never happen. The only thing I can figure out is that the SQL Server Management Tool is somehow correct setting up the connection and is inherited by same user logins. It's really strange actually.
I wouldn't think so - this setting seems to be specific to using SQL transactions or not.
I don't see that having SSMS open should keep a ## temp table (assuming it is a ## temp table and not # temp table) alive that eConnect created and is using. The reason is that the ## temp tables are connection related and your SSMS connection the eConnect one should be different if using the API.
Unless perhaps you are calling the procs directly from SSMS? If so then yes they would be on the same connection and could potentially kep alive due to that.
Posting from Mark Polino at DynamicAccounting.net
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.