Troubleshooting Transaction Errors When Integrating Using the eConnect API

Hello community,

Today's blog post is going to be sharing some knowledge regarding a specific code path that can lead to the following error message being logged in Event Viewer each time a document is created.

Distributed Transaction was used
This could be caused by new connection strings used within each xml document, but reusing the base transaction scope. 
Configuration Setting 'ReuseBaseTransaction' is by default FALSE. Remove this configuration setting, or set it to FALSE if this was not the expected behavior.

In short, this error can be suppressed by setting the following value in your app.config:

<appSettings>
 <add key="LogDtcPromotions" value="false"/>
</appSettings>

Let's look at a code sample that can cause a transaction to escalate to DTC when using the eConnect API:

try
{
    using (TransactionScope scope = new TransactionScope())
    {
        using (SqlConnection connection1 = new SqlConnection(sConnectionString))
        {
            connection1.Open();
            SqlCommand command1 = new SqlCommand("SELECT * FROM DYNAMICS..SY01500", connection1);
            returnValue = command1.ExecuteNonQuery();
            using (SqlConnection connection2 = new SqlConnection(sConnectionString))
            {
                connection2.Open();
                returnValue = 0;
                SqlCommand command2 = new SqlCommand("SELECT * FROM DYNAMICS..SY01400", connection2);
                returnValue = command2.ExecuteNonQuery();
                salesOrder = eConCall.CreateTransactionEntity(sConnectionString, salesOrderDocument);
                connection2.Close();
            }
            connection1.Close();                                
        }
        scope.Complete();
    }
}
catch (TransactionAbortedException ex)
{
    writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
}

You would start by bundling the entire operation into a single TransactionScope object. This would allow you to ensure the eConnect calls only get fully committed if there isn't a problem returned by the eConnect procedures. In order to get a transaction to escalate to DTC, you have to open two connections to SQL in the same TransactionScope. As you can see in the code above, I am opening two separate connections to query two separate tables in my database. I'm not actually doing anything with the data in my example, but since I'm not closing the connections before making the eConnect call, this transaction would be escalated to Distributed Transaction Coordinator and the error would log unless it were suppressed in the app.config.

https://docs.microsoft.com/en-us/previous-versions/ms229978(v=vs.90)

https://docs.microsoft.com/en-us/dotnet/api/system.transactions.transactionscope?redirectedfrom=MSDN&view=netframework-4.7.2

A typical use case scenario of this would be pulling header information from one table, line information from another, building the document, and then calling eConnect to create the document in GP. Escalating transactions through DTC can lead to better stability, but this will be a pretty substantial performance hit. My sample integration went from creating thousands of documents in less than a minute to nearly 30 seconds PER document when they were escalated through to DTC.

The easy workaround is to close each connection after the data has been pulled. You can have multiple connections in the same TransactionScope, but if the first isn't closed before the second is opened then you will get escalated. Using TransactionScope objects like this can lead to other errors that I want to point out.

"The transaction is in doubt." 
"The transaction has aborted."

If you run into these errors, I have two main recommendations. First, make sure you're disposing the eConnect connection object itself for each new document.

try
{                              
    salesOrder = eConCall.CreateTransactionEntity(sConnectionString, salesOrderDocument);
}
catch (eConnectException exp)
{
    MessageBox.Show(exp.ToString());
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}
finally
{
    eConCall.Dispose();
}

Second, you may want to try increasing the timeout value of the system.transactions namespace in your app.config.

<configuration>
  <system.transactions> <defaultSettings timeout="00:04:00"/> </system.transactions>
 <appSettings>
 </appSettings>
 <startup>
 </startup>
</configuration>

If your overall transaction would exceed 60 seconds which is the default, even if there were no SQL errors you could fault on the local transaction timeout so increasing this may be the only way around the error if breaking up the batch isn't a possibility. If you're creating all transactions in a batch before committing the entire batch, setting the timeout may be necessary.

Best of luck with your future integrations!