Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction

ev.nexus · May 9, 2018, 7:42am

Below is a piece of code I wrote for an external BPM. Basically, it should work like this query in SQL:

SELECT pc.StdMaterialCost, pc.StdMtlBurCost, p.ShortChar01 AS SkuType, od.PartNum
FROM
	OrderDtl od
LEFT OUTER JOIN
	Part p
ON
	p.PartNum = od.PartNum
AND
	p.Company = od.Company
LEFT OUTER JOIN
	PartCost pc
ON
	pc.PartNum = od.PartNum
AND
	pc.Company = od.Company
WHERE
	od.Company = 'COMP01'
AND
	od.OrderNum = 700118
AND
	pc.CostID = '1'
AND
	p.ShortChar01 NOT IN ('G', 'X')

The query works and returns me the exact results I expected. So when I transformed to C# code it looked like this:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.ServiceModel;
using Epicor.Data;
using Ice;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace SalesOrder.BPM
{
    public partial class Update
    {
        public void StdCostValidation(Erp.Tablesets.SalesOrderTableset ds)
        {
            var ctx = new Erp.ErpContext();
            var orderHed = ds.OrderHed.First();

            var listOfCosts = ctx.OrderDtl.With(LockHint.NoLock).Join(ctx.Part.With(LockHint.NoLock),
                                            dtl => new { dtl.PartNum, dtl.Company }, 
                                            part => new { part.PartNum, part.Company },
                                            (dtl, part) => new { dtl, part })
                                            .Join(ctx.PartCost.With(LockHint.NoLock),
                                                    dtlPart => new { dtlPart.dtl.PartNum, dtlPart.dtl.Company },
                                                    cost => new { cost.PartNum, cost.Company },
                                                    (dtlPart, cost) => new { dtlPart, cost })
                                                    .Where(a =>
                                                        a.dtlPart.part.ShortChar01 != "G" &&
                                                        a.dtlPart.part.ShortChar01 != "X" &&
														a.cost.CostID == "1" &&
														a.dtlPart.dtl.Company == orderHed.Company &&
                                                        a.dtlPart.dtl.OrderNum == orderHed.OrderNum)
                                                    .Select(x => new StandardClass
                                                    {
                                                        PartNum = x.dtlPart.part.PartNum,
														SkuType = x.dtlPart.part.ShortChar01,
                                                        TotalStdCost = (x.cost.StdMaterialCost + x.cost.StdMtlBurCost)
                                                    })
													.ToList();

            foreach (var cost in listOfCosts)
            {
				if (cost.TotalStdCost == 0M)
                {
                    throw new BLException(string.Format("Total standard cost cannot for {0} be zero", cost.PartNum));
                }
            }
        }
    }

    public class StandardClass
    {
        public string PartNum;
        public string SkuType;
        public decimal TotalStdCost;
    }
}

The problem is, everytime I run this code I get the error:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

What am I doing wrong?

Full error log:

Application Error

Exception caught in: Epicor.ServiceModel

Error Detail 
============
Message: An error occurred while executing the command definition. See the inner exception for details.
Inner Exception Message: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Program: Epicor.ServiceModel.dll
Method: ShouldRethrowNonRetryableException

Client Stack Trace 
==================
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Epicor.Data.DBExpressionCompiler.GetResult[TContext,TQuery,TResult](Func`3 executeQuery, Cache cacheSetting, TContext dataContext, TQuery query) in D:\_Releases\ICE\3.1.500.36\Source\Framework\Epicor.System\Data\DBExpressionCompiler.cs:line 425
   at Epicor.Data.DBExpressionCompiler.InvokeList[TContext,TQuery,TResult](Expression expression, Cache currentCacheSetting, Boolean cacheQuery, TContext dataContext, Func`2 getDataCacheKey, Func`2 compileQuery, Func`3 executeQuery) in D:\_Releases\ICE\3.1.500.36\Source\Framework\Epicor.System\Data\DBExpressionCompiler.cs:line 159
   at Epicor.Data.DBExpressionCompiler.<>c__DisplayClass2_0`4.<Compile>b__0(TContext context, TArg1 arg1, TArg2 arg2) in D:\_Releases\ICE\3.1.500.36\Source\Framework\Epicor.System\Data\DBExpressionCompiler.Generated.cs:line 82
   at Erp.Services.BO.SalesOrderSvc.chkComplianceOrderFail(Int32 orderNum, String& compliantMsg) in C:\_Releases\ERP\UD10.1.500.36\Source\Server\Services\BO\SalesOrder\SalesOrder.cs:line 13185
   at Erp.Services.BO.SalesOrderSvc.MasterUpdate(Boolean lCheckForOrderChangedMsg, Boolean lcheckForResponse, String cTableName, Int32 iCustNum, Int32 iOrderNum, Boolean lweLicensed, Boolean& lContinue, String& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel, SalesOrderTableset& ds) in C:\_Releases\ERP\UD10.1.500.36\Source\Server\Services\BO\SalesOrder\SalesOrder.cs:line 21046
   at Erp.Services.BO.SalesOrderSvcFacade.MasterUpdate(Boolean lCheckForOrderChangedMsg, Boolean lcheckForResponse, String cTableName, Int32 iCustNum, Int32 iOrderNum, Boolean lweLicensed, Boolean& lContinue, String& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel, SalesOrderTableset& ds) in C:\_Releases\ERP\UD10.1.500.36\Source\Server\Services\BO\SalesOrder\SalesOrderSvcFacade.cs:line 2401
   at SyncInvokeMasterUpdate(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at Epicor.Hosting.OperationBoundInvoker.InnerInvoke(Object instance, Func`2 func) in D:\_Releases\ICE\3.1.500.36\Source\Framework\Epicor.System\Hosting\OperationBoundInvoker.cs:line 59
   at Epicor.Hosting.OperationBoundInvoker.Invoke(Object instance, Func`2 func) in D:\_Releases\ICE\3.1.500.36\Source\Framework\Epicor.System\Hosting\OperationBoundInvoker.cs:line 47
   at Epicor.Hosting.Wcf.EpiOperationInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) in D:\_Releases\ICE\3.1.500.36\Source\Framework\Epicor.System\Hosting\Wcf\EpiOperationInvoker.cs:line 23
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Inner Trace:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
:   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

   at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets)
   at Erp.Proxy.BO.SalesOrderImpl.MasterUpdate(Boolean lCheckForOrderChangedMsg, Boolean lcheckForResponse, String cTableName, Int32 iCustNum, Int32 iOrderNum, Boolean lweLicensed, Boolean& lContinue, String& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel, SalesOrderDataSet ds)
   at Erp.Adapters.SalesOrderAdapter.MasterUpdate(Boolean lCheckForOrderChangedMsg, Boolean lcheckForResponse, String cTableName, Int32 iCustNum, Int32 iOrderNum, Boolean lweLicensed, Boolean& lContinue, String& cResponseMsg, String& cCreditShipAction, String& cDisplayMsg, String& cCompliantMsg, String& cResponseMsgOrdRel)
   at Erp.UI.App.SalesOrderEntry.Transaction.Update()

Inner Exception 
===============
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

Jonathan · May 9, 2018, 4:21pm

I don’t really use BPMs so not sure if this is the correct way to fix it but try wrapping the query in a transaction scope and see if it works.

using (TransactionScope trans = ErpContext.CreateDefaultTransactionScope())
{
//query here
trans.Complete();
}

Bart_Elia · May 10, 2018, 1:14am

You should never ‘new’ up a db context directly. I am traveling so will need to expand the chat in the future. Basically you are confusing sql server by standing up another connection to the db. ADO.Net thinks you are now trying to coordinate a transaction across multiple dbs so looks for the MSDTC service (Microsoft Distributed Transaction Coordinator) to manage the two phase commit between ERP and whatever other db you are connected to - it could be MySQL, Oracle, or another SQL Instance,
Some folks think the solution is to turn on the service - which is fine in some scenarios - but you really are probably looking to stay within the same transaction. You can corrupt yourself real fast by crossing instances with different values in each data model on top of the performance penalty of using MSDTC,

I don’t have the code in front of me but I believe the class you want is
Ice.Services.ContextFactory
There are a couple of methods to construct data models but I think you want
CreateContext()

More background to follow or track me down at Insights. I am still trying to gather notes on all the meetings I had here at MS Build so a little harried atm.

hkeric.wci · May 10, 2018, 1:30am

If I recall there were special Constructor Requirements one should call. Then you have access to the ctx passed from Epicor to your DLL - if you are invoking this from Epicor.

Bart_Elia · May 10, 2018, 6:00am

I love my CtxBoundBase class. Its been awhile since I did external DLLs so I’ll defer to your recent usage. Under the covers we are doing what I mentioned previously - just automagically for you.

ev.nexus · May 10, 2018, 8:41am

Hi Bart, thank you for your reply. So, if I got it correctly, the context would look like this?

var ctx = ContextFactory.CreateContext<Erp.ErpContext>();

Bart_Elia · May 10, 2018, 2:46pm

Wrapped in a using, looks about right. If I have time on the train today I’ll see if I can whip something up.

Topic		Replies	Views
Network access for Distributed Transaction Manager (MSDTC) has been disabled Kinetic ERP bpm	12	3947	October 17, 2022
Error Help - uBAQ - This platform does not support distributed transactions Kinetic ERP epicor , bpm , baq , serial-numbers	23	3834	September 6, 2023
2 different transactions in a UBAQ BPM Epicor ERP 10 bpm , ubaq	2	1869	January 22, 2021
E10 BPM External Code, use DB and bpmContext Epicor ERP 10 e10	11	3373	November 9, 2016
Data Directive BPM C# Linq Help Epicor ERP 10	51	10947	November 30, 2018

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction

Related Topics