The underlying provider failed on Open - Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached

Dhanalakshmi · November 18, 2024, 11:46am

Hi,

I am getting the below error when running the Function.
Written a Function for loading the BAQ and then update the ABC Code for all the rows in the BAQ using BO objects.

BAQ returns 80000 records. Error occurs after 1100 records

Error details:
Ice.Common.BusinessObjectException: Program Ice.Services.Lib.RunTask when executing task 14691534 raised an unexpected exception with the following message: RunTask:
System.Data.Entity.Core.EntityException: The underlying provider failed on Open.
 ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Epicor.Data.Provider.EpiConnection.Open() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Framework\Epicor.System\Data\EpiProvider2\EpiConnection.cs:line 241
   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.Open(DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
   at Ice.Services.ContextFactory.CreateContext() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Framework\Epicor.System\Services\ContextFactory.cs:line 70
   at Ice.Services.ContextFactory.CreateContext[TDataContext]() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Framework\Epicor.System\Services\ContextFactory.cs:line 39
   at Ice.Core.WriteTaskLog.WriteToTaskLog(String newMessage, Int64 taskNum, Boolean isError) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\TaskBase\WriteTaskLog\WriteTaskLog.cs:line 39
   at Ice.Internal.Task.ScheduledFunction.ExecuteFunction.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Internal\Task\ScheduledFunction\ExecuteFunction.cs:line 74
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 68
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 64
   at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
   at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 450
Program Ice.Services.Lib.RunTask when executing task 14691534 raised an unexpected exception with the following message: RunTask:
System.Data.Entity.Core.EntityException: The underlying provider failed on Open.
 ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Epicor.Data.Provider.EpiConnection.Open() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Framework\Epicor.System\Data\EpiProvider2\EpiConnection.cs:line 241
   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.Open(DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
   at Ice.Services.ContextFactory.CreateContext() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Framework\Epicor.System\Services\ContextFactory.cs:line 70
   at Ice.Services.ContextFactory.CreateContext[TDataContext]() in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Framework\Epicor.System\Services\ContextFactory.cs:line 39
   at Ice.Core.WriteTaskLog.WriteToTaskLog(String newMessage, Int64 taskNum, Boolean isError) in C:\_releases\ICE\ICE4.2.200.11\Source\Server\Internal\Lib\TaskLib\TaskBase\WriteTaskLog\WriteTaskLog.cs:line 39
   at Ice.Services.Lib.RunTaskSvc.SetTaskStatusForError(SysTask sysTask, Int64 taskNumber) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 1178
   at Ice.Services.Lib.RunTaskSvc.SetTaskStatus(String status, Int64 taskNumber, Boolean clearActivityMessage, Boolean isAPRTask) in C:\_releases\ICE\ICE4.2.200.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 908
   at Ice.Cloud.ProxyBase`1.CallWithCommunicationFailureRetry(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, RestRpcValueSerializer serializer) in C:\_releases\ICE\ICE4.2.200.5\Source\Shared\Framework\Epicor.ServiceModel\Cloud\ProxyBase.cs:line 725
   at Ice.Cloud.ProxyBase`1.CallWithMultistepBpmHandling(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, Boolean useSparseCopy) in C:\_releases\ICE\ICE4.2.200.5\Source\Shared\Framework\Epicor.ServiceModel\Cloud\ProxyBase.cs:line 670
   at Ice.Cloud.ProxyBase`1.Call(String methodName, ProxyValuesIn valuesIn, ProxyValuesOut valuesOut, Boolean useSparseCopy) in C:\_releases\ICE\ICE4.2.200.5\Source\Shared\Framework\Epicor.ServiceModel\Cloud\ProxyBase.cs:line 649
   at Ice.Proxy.Lib.RunTaskImpl.RunTask(Int64 ipTaskNum) in C:\_releases\ICE\ICE4.2.200.0\Source\Shared\Contracts\Lib\RunTask\RunTaskProxy.cs:line 63
   at Ice.TaskAgentCore.ImplCaller.RunTaskImplCaller`1.<>c__DisplayClass4_0.<Call>b__0(TImpl impl) in C:\_releases\ICE\ICE4.2.200.5\Source\TaskAgent\TaskAgentCore\ImplCaller\RunTaskImplCaller.cs:line 47
   at Ice.TaskAgentCore.ImplCaller.RunTaskImplCaller`1.Call[TResult](Func`2 doWork, ExceptionBehavior communicationExceptionBehavior, ExceptionBehavior timeoutExceptionBehavior, Boolean isContinuousProcessingTask) in C:\_releases\ICE\ICE4.2.200.5\Source\TaskAgent\TaskAgentCore\ImplCaller\RunTaskImplCaller.cs:line 150
   at Ice.TaskAgentCore.ImplCaller.RunTaskImplCaller`1.Call(Action`1 doWork, ExceptionBehavior communicationExceptionBehavior, ExceptionBehavior timeoutExceptionBehavior, Boolean isContinuousProcessingTask) in C:\_releases\ICE\ICE4.2.200.5\Source\TaskAgent\TaskAgentCore\ImplCaller\RunTaskImplCaller.cs:line 52
   at Ice.TaskAgentCore.ScheduleProcessor.CallServiceAction(SysTaskRow sysTaskRecord, SysTaskParamRow companyParamRecord, ServiceCallArguments serviceCallArguments, Boolean isContinuousStartupTask) in C:\_releases\ICE\ICE4.2.200.5\Source\TaskAgent\TaskAgentCore\ScheduleProcessor.cs:line 948

kve · November 18, 2024, 12:25pm

Can you share more about the function? A screen cap of any widgets and the custom code would be helpful.

Dhanalakshmi · November 18, 2024, 1:09pm

Update ABC Code Widget Code

giCount++;
gstrLogMsg = giCount + " Update ABC " + gDSPartWhse.Tables[0].Rows.Count + " " + DateTime.Now + Environment.NewLine;
string outstrMsg = "";

var context = (Erp.ErpContext)Ice.Services.ContextFactory.CreateContext();
var erpContext = new Erp.Internal.Lib.CCredChk(context);

string strCompany = "";
string strPartNum = "";
string strPlant = "";
string strWhseCode = "";
string strNewSystemABC = "";
  
      
try
{
  if(gDSPartWhse.Tables[0].Rows.Count  > 0)
  {
    DataRow drRow = gDSPartWhse.Tables[0].Rows[0];
    
    strCompany = drRow["Company"].ToString();
    strPartNum = drRow["Part"].ToString();
    strPlant = drRow["Plant"].ToString();
    strWhseCode = drRow["WhseCode"].ToString();
    strNewSystemABC = drRow["NewSystemABC"].ToString();
      
    outstrMsg = "    Writing Status File Part: " + strPartNum + " Plant: "  + strPlant + " Whse: " + strWhseCode + " NewABC: " + strNewSystemABC;
    
    using (var txScope = IceContext.CreateDefaultTransactionScope())
    {

      this.CallService<Erp.Contracts.PartSvcContract>(psc => {
            gstrLogMsg += "    Bef GetByID '" + strPartNum + "', " + strPlant + ", " + strWhseCode + ", " + strNewSystemABC  + Environment.NewLine;
            
            Erp.Tablesets.PartTableset pds = psc.GetByID(strPartNum);
            gstrLogMsg += "    Aft GetByID '" + strPartNum + "'" + Environment.NewLine;
            
            var whse = (from xRow in pds.PartWhse
                                   where xRow.Company == strCompany && xRow.PartNum == strPartNum 
                                   && xRow.WarehouseCode == strWhseCode && xRow.Plant == strPlant
                                   select xRow).FirstOrDefault();
            
            gstrLogMsg += "    Partwhse: " + whse + Environment.NewLine;
            
            bool bIsUpdate = false;
            if(whse != null)
            {
                gstrLogMsg += "     Cmp: " + whse.Company + ", Part: '" + whse.PartNum + "', Plant: " + whse.Plant + ", Manual: " + whse.ManualABC + ", SystemABC: " + whse.SystemAbc + ", CalABC: " + strNewSystemABC + Environment.NewLine;
            
                whse.ManualABC = true;
                whse.SystemAbc = strNewSystemABC;
                whse.RowMod = "U";
                
                bIsUpdate= true;
            }
            
            if(bIsUpdate)
              psc.Update(ref pds);
              
            gstrLogMsg += " Update PartWhse " + bIsUpdate + ", NewABC: " + strNewSystemABC + Environment.NewLine;
            whse = null;
            pds = null;
            psc = null;
      });//CallService 
      txScope.Complete();
      txScope.Dispose();
    }
  }
  
  var vDbPartWhseExists = (from xRow in erpContext.Db.PartWhse
                                     where xRow.Company == strCompany && xRow.PartNum == strPartNum 
                                     && xRow.WarehouseCode == strWhseCode
                                     select xRow).FirstOrDefault();
                                     
  gstrLogMsg += " DbPartWhse: " + (vDbPartWhseExists != null ? vDbPartWhseExists.SystemAbc : " NotExist" ) + Environment.NewLine;
  
  System.IO.File.AppendAllText(gstrLogFilePath, gstrLogMsg + Environment.NewLine);
}
catch(Exception ex)
{
  gstrLogMsg += "Error at ABCUpdateFunc - Update ABC: " + ex.Message + Environment.NewLine + ex.InnerException  + " " + DateTime.Now + Environment.NewLine;

  outstrMsg += " Error:" +  ex.Message + Environment.NewLine;
  
  System.IO.File.AppendAllText(gstrLogFilePath, gstrLogMsg);
}

utaylor · November 18, 2024, 1:27pm

Seems like it could be a never ending loop.

Dhanalakshmi · November 18, 2024, 1:31pm

No it’s not a loop. I will delete the row in the wigdet “Delete in DS” once I processed it.

utaylor · November 18, 2024, 1:56pm

Ok

Dhanalakshmi · November 18, 2024, 2:00pm

When I limit the data to 1800, it’s working. Error occurs when I change the limit to 1900

Exactly the error is at the row 1884

kve · November 18, 2024, 2:01pm

I’m not able to spot anything wrong at a glance, but is there a reason you’re looping through 80K BAQ Results instead of just uploading via DMT?

Dhanalakshmi · November 18, 2024, 2:03pm

New ABC Code value is determined in the BAQ depends on the condition so i can’t do DMT

kve · November 18, 2024, 2:04pm

Also, rather than creating the context in code to check your partwhse record, I would give the library database read permissions and add the PartWhse table as a read only table.

Is your debug file at gstrLogFilePath catching this error? or is the error only in the task?

kve · November 18, 2024, 2:06pm

Again, why not? If you copy the BAQ to a CSV file, or export the BAQ results to a CSV, what’s the difference between calling the results here?

Dhanalakshmi · November 18, 2024, 2:07pm

I used the erpContext for checking the data whether the changes in the Db or not. But I will remove and check

gstrLogFilePath is catching the error exactly when the PartWhse.Update is called and the Task also show Error

utaylor · November 18, 2024, 2:10pm

As @kve said, if you run the BAQ and get the results then you can export it to CSV and DMT it.

It will have gone through your BAQ at that point and gotten the ABC code you calculated via your BAQ.

In other words, by running the BAQ, exporting results to CSV, and then DMTing it, you have essentially done the same thing you are trying to do in code.

Is this a one time endeavor or are you planning on doing this workflow many many times?

Dhanalakshmi · November 18, 2024, 2:23pm

OK I will try and let you guys know. Thanks for your help

Dhanalakshmi · December 3, 2024, 2:10pm

@utaylor @kve Thank You for your help. It’s working.

But I am getting an error for some parts

Error: Part is a key column and cannot be changed

Do you know what’s causing this error for some parts

kve · December 3, 2024, 2:13pm

What tables are you updating? The only time I’ve seen that is when someone tries to change a part number, which can’t be done.

utaylor · December 3, 2024, 2:24pm

I have seen it where if your part’s start with a 0, excel removes it…

That, or maybe you have a space at the end of your part.

Just check the formatting on your part column in your excel sheet and make sure it is text type.

Dhanalakshmi · December 4, 2024, 6:52am

I tried in the Function for a single part. Still the error is coming

utaylor · December 4, 2024, 1:14pm

What are you trying to do again? Assign an ABC code?

Dhanalakshmi · December 5, 2024, 11:37am

Ya I tried to update the ABC Code and Manual ABC