MRP Failing with "Underlying provider failed on EnListTransaction." "The requested operation cannot be completed because the connection has been broken."

[bmartin (Blake Martin)]

Hello All,

We have been experiencing issues during MRP. The above error is nearly always the culprit. It has gotten to the point where it is a coin flip on whether our Net Change or Regen completes. For our setup we have a SQL server and two App Servers on separate machines. Our MRP runs on the second App Server, but we have attempted to run it on the first App Server with the same results.

We have implemented various Method and Data Directives in the past months, so have begun to look into those. On our Pilot server, we have performed the steps in this article to disable all Method and Data Directives. This still failed two out of three times:

Turn off all bpms E10

Good Morning, When we were on E9 we could easily turn off all bpms for testing on our development database by changing the name of the bpm folder. It was a terrific way to check whether a bug had anything to do with our bpms. Is there any suggested “easy-ish” way to turn off bpms in one fell swoop? If not, is there any suggested means of doing it rather than going through each method directive, data directive individually? Thanks Nancy

One interesting aspect in this case is that a second, smaller, company that we run MRP for from the same setup fails with the same error. It does not occur with near the same frequency. We have not implemented the Directives in this company,and it is not near as large from a part and job perspective.

A sample stack trace from MRP is below. The MRP Method called when this error occurs varies. Any ideas on where we should focus our attention? We would think it has something to do with the new Method and Data Directives put in place, but cannot say for sure. Has anybody else seen a similar error, and what was the ultimate cause? Thanks in advance for the help and Happy Holidays!

13:16:06 The underlying provider failed on EnlistTransaction.
13:16:06 The requested operation cannot be completed because the connection has been broken.
13:16:06 The underlying provider failed on EnlistTransaction.
13:16:06    at System.Data.Entity.Core.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
   at System.Data.Entity.Core.Objects.ObjectContext.EnsureContextIsEnlistedInCurrentTransaction[T](Transaction currentTransaction, Func`1 openConnection, T defaultValue)
   at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at Ice.IceDataContext.SaveChanges(SaveOptions options) in C:\_Releases\ICE\ICE3.2.200.11\Source\Framework\Epicor.System\Data\IceDataContext.cs:line 356
   at Ice.IceDataContext.Validate[TLinqRow](TLinqRow row) in C:\_Releases\ICE\ICE3.2.200.11\Source\Framework\Epicor.System\Data\IceDataContext.cs:line 317
   at Ice.IceDataContext.Release[TEntity](TEntity& entity) in C:\_Releases\ICE\ICE3.2.200.11\Source\Framework\Epicor.System\Data\IceDataContext.cs:line 269
   at Erp.Internal.Lib.DeferredUpdate.PostPQDemand(String pComp, String pPartNum, String pWhseCode, Decimal pDemandQty, Decimal pReservedQty, Decimal pAllocatedQty, Decimal pPickingQty, Decimal pPickedQty, Decimal pUnfirmQty, String pUOM, String pDemandType) in c:\_Releases\ERP\RL10.2.200.0\Source\Server\Internal\Lib\DeferredUpdate\DeferredUpdate.cs:line 547
   at Erp.Internal.Lib.JobMtlBackOut._BackOut(String ipCompany, String ipPartNum, String ipWarehouseCode, String ipIUM, Decimal ipRequiredQty, Decimal ipIssuedQty, Boolean ipJobFirm) in C:\_Releases\ERP\UD10.2.200.11\Source\Server\Internal\Lib\JobMtlShared\BackOut\BackOut.cs:line 86
   at Erp.Internal.MR.MrpExp.deleteUnfirmJob(String vPlantList) in C:\_Releases\ERP\UD10.2.200.11\Source\Server\Internal\MR\MrpExp\MrpExp.cs:line 1989
   at Erp.Internal.MR.MrpExp.main_block(List`1 ttMrpProcRows, List`1 ttMrpQueueRows) in C:\_Releases\ERP\UD10.2.200.11\Source\Server\Internal\MR\MrpExp\MrpExp.cs:line 2620
   at Erp.Internal.MR.MrpExp.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ERP\UD10.2.200.11\Source\Server\Internal\MR\MrpExp\MrpExp.cs:line 919
13:16:06 The requested operation cannot be completed because the connection has been broken.
13:16:06    at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlDelegatedTransaction.Initialize()
   at System.Transactions.TransactionStatePSPEOperation.PSPEInitialize(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Guid promoterType)
   at System.Transactions.TransactionStateActive.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction, Guid promoterType)
   at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Guid promoterType)
   at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.EnlistTransaction(Transaction transaction)
   at System.Data.SqlClient.SqlConnection.EnlistTransaction(Transaction transaction)
   at Epicor.Data.Provider.EpiConnection.EnlistTransaction(Transaction transaction) in C:\_Releases\ICE\ICE3.2.200.11\Source\Framework\Epicor.System\Data\EpiProvider2\EpiConnection.cs:line 208
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.EnlistTransaction(DbConnection connection, EnlistTransactionInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
13:16:06 Unexpected Error - Cancelling MRP - MrpExp
13:16:06 The underlying provider failed on EnlistTransaction.
13:16:06    at System.Data.Entity.Core.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)
   at System.Data.Entity.Core.Objects.ObjectContext.EnsureContextIsEnlistedInCurrentTransaction[T](Transaction currentTransaction, Func`1 openConnection, T defaultValue)
   at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at Ice.IceDataContext.SaveChanges(SaveOptions options) in C:\_Releases\ICE\ICE3.2.200.11\Source\Framework\Epicor.System\Data\IceDataContext.cs:line 356
   at Erp.Internal.MR.MrpExp.abortMRP(Int64 instanceTaskNum) in C:\_Releases\ERP\UD10.2.200.11\Source\Server\Internal\MR\MrpExp\MrpExp.cs:line 985
   at Erp.Internal.MR.MrpExp.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ERP\UD10.2.200.11\Source\Server\Internal\MR\MrpExp\MrpExp.cs:line 952
13:16:06 The requested operation cannot be completed because the connection has been broken.
13:16:06    at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlDelegatedTransaction.Initialize()
   at System.Transactions.TransactionStatePSPEOperation.PSPEInitialize(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Guid promoterType)
   at System.Transactions.TransactionStateActive.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction, Guid promoterType)
   at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Guid promoterType)
   at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
   at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   at System.Data.SqlClient.SqlInternalConnection.EnlistTransaction(Transaction transaction)
   at System.Data.SqlClient.SqlConnection.EnlistTransaction(Transaction transaction)
   at Epicor.Data.Provider.EpiConnection.EnlistTransaction(Transaction transaction) in C:\_Releases\ICE\ICE3.2.200.11\Source\Framework\Epicor.System\Data\EpiProvider2\EpiConnection.cs:line 208
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action`2 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.EnlistTransaction(DbConnection connection, EnlistTransactionInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityConnection.EnlistTransaction(Transaction transaction)

[hkeric.wci (Haso Keric)]

Personally I would start with an upgrade, because MRP Bugs can be nasty! We even had to upgrade to a Major version - due to the same bug. We are now on 10.2.300+

Here is your change log.

Also you might want to enable Verbose so you can atleast see a little more info like which PartNum, JobNum.

[bmartin (Blake Martin)]

Thanks for the response, Haso. Although an upgrade may be the final solution, in the short term it may not be a viable option. Our upgrade process takes a little while

After some testing in our Pilot environment and delving into the logs, we have stumbled into a specific line of the mrp log that it repeatedly fails at. It is in the portion of MRP Regen that deletes unfirm jobs, and is below:

09:59:48 Site: SITE After Delete of Table: PcValueSet for JobMtl
10:04:38 The underlying provider failed on EnlistTransaction.
10:04:38 The requested operation cannot be completed because the connection has been broken.
10:04:37 The underlying provider failed on EnlistTransaction.

We are wondering if there is a data related issue in that plant (part/routing/job). We were completing successful regens with some regularity until a few weeks to a month ago, so it seems something changed. If anybody has any ideas, we have open ears.

We have a ticket open with Epicor, so if anything comes up from that case we will post here. The help is very much appreciated.

[JasonMcD (Jason McDermott)]

Are you on-premises? I would do a DBCC check in SSMS if so…

[RonR (Ron Rispens)]

So we consistently have this problem… One thing that helped was giving MRP a special login and making sure that user fires no BPM’s. The problem still exists and we are on 2023.1.4. and that is with trying everything you have all mentioned here and of course support keeps telling us to turn off BPM’s