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

Kinetic ERP
,
1

Hi

A previous post (System Task fails with timeout for scheduled Epicor Function) covered the same issue but didn’t reach a conclusion so I’m posting again in case anybody has solved it more recently.

For a block of C# code when running by using “Schedule Epicor Function” it terminates with the following error:

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.

This code loops over quote lines and refreshes the engineering method. Code all works fine for 40 quotes (each with approx 15 lines, and the quote line engineering method does have a couple of hundred materials). The error seems understandable, when it has reached a certain number of iterations the connection pool is exhausted - but I can’t see why.

Here is the code:

var qteHeadList = Db.QuoteHed.Where(q => q.Company == CompanyID && q.QuoteClosed == false && q.QuoteNum <= 10039).ToList();

foreach(var qteHead in qteHeadList)
{
    var vQuoteNum = qteHead.QuoteNum;
    var vPlant = Db.Customer.Where(c => c.Company == Session.CompanyID && c.CustNum == qteHead.CustNum).Select(c => c.CustID).FirstOrDefault();
    
    var qteDtlList = Db.QuoteDtl.Where(q => q.Company == CompanyID && q.QuoteNum == vQuoteNum).ToList();
    
    foreach(var qteDtl in qteDtlList)
    {        
        var vQuoteLine = qteDtl.QuoteLine;
        var vQuotePartNum = qteDtl.PartNum;
        var vPartRev = qteDtl.RevisionNum;
        var vTargetAsm = 0;
        var vMsg = "";
        var vProcessSkip = "";
        var dToday = BpmFunc.Today();
        
        bool vPartRevApproved = Db.PartRev.Any(r => r.Company == CompanyID && r.PartNum == vQuotePartNum && r.RevisionNum == vPartRev && r.Approved);
                
        if(vPartRevApproved)
        {               
            using (CallContext.Current.TemporarySessionCreator.SetPlantID(vPlant).Create())
            {
                this.CallService<Erp.Contracts.QuoteAsmSvcContract>(qteCon =>
                {
                    try
                    {   
                        qteCon.DeleteAllAssembly(vQuoteNum, vQuoteLine);
                        qteCon.GetDetails(vQuoteNum, vQuoteLine, vTargetAsm, "Method", 0, 0, "", 0, vQuotePartNum, vPartRev, "", false, false, false, out vMsg);      
                    }
                    catch
                    {                    
                    }
                    
                    vProcessSkip = "Processed method refresh";
                });
            }                    
        }  
        else
        { 
            vProcessSkip = "Skipped, revision not approved";
        }
        
        this.CallService<Ice.Contracts.SysTaskSvcContract>( stCon =>
        {
            bool morePages;
            string whereClauseSysTask = "TaskStatus = 'Active'";
            string whereClauseSysTaskParam = "ParamName = 'FunctionId' and ParamCharacter = 'RefreshQuoteEngineeredMethod'"; 
            
            var dsSysTask = new Ice.Tablesets.SysTaskTableset();
            
            dsSysTask = stCon.GetRows(whereClauseSysTask, whereClauseSysTaskParam, 0, 1, out morePages);
            
            foreach(var task in dsSysTask.SysTaskParam)
            {
                string message = $"{vProcessSkip} - Quote {vQuoteNum}/{vQuoteLine}";
            
                this.CallService<Ice.Contracts.SysMonitorTasksSvcContract>(sm => {sm.WriteToTaskLog(message, task.SysTaskNum, Epicor.ServiceModel.Utilities.MsgType.Info);});
        
            }      
        
        });
        
    }
}

Is there something with my code pattern that I can change/improve to resolve this? Anybody else running into this problem when using Schedule Epicor Function?

Any ideas gratefully received! Thanks for reading.

2

Since you call this service every iteration (regardless of whether the part is approved)
this.CallService<Ice.Contracts.SysTaskSvcContract>
so would it be reasonable to keep that connection open and perform your foreach within that SysTask service call? First checking if qteDtlList has any rows…

1 Like