eConnect 10: Connection Pool Timeout Error caused by GetNextGLJournalEntryNumber

By Steve Endow

Several months ago I developed three eConnect 10 integrations for a client to import GL journal entries, AP invoices, and Inventory transactions into Microsoft Dynamics GP.  The integrations are scheduled to run nightly, and all were working fine.

At the end of the year, the client received the following error notification from the GL JE import:

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.

It was the first time this error had occurred, and I was unable to reproduce the error.  The client asked if the 30 year end journal entries contributed to the problem, but I assured them that the integration should be able to process thousands of transactions continuously without any issues.

At the time, I assumed it was a random issue, perhaps a network or SQL Server issue that contributed to the error.  I was able to run the integration and process all of the pending data without issue.

Almost one month later, the client notified me this week that the error occurred again.  At this point, I had to assume that it was not a random error.  This time the error occurred when there were about 80 journal entries waiting to be imported.

While reviewing my integration log files, I noticed another error that accompanied the timeout error:

Error in method GetNextNumber

Although this error did not seem to appear in conjunction with the timeout error, every JE that was imported after the timeout error occurred failed to import because of the GetNextNumber issue.  So, I decided to look into the GetNextNumber issue further.

My code doesn't have a routine called GetNextNumber, and doesn't ever call a method called GetNextNumber.  But, I am using the GetNextDocNumbers eConnect object to call the GetNextGLJournalEntryNumber method to get my JE numbers, so that is where I started.

I wrote a simple loop that repeatedly calls the GetNextGLJournalEntryNumber method.  Sure enough, after running the loop routine a few times, I received the Timeout Expired error.  Any subsequent attempt to call GetNextGLJournalEntryNumber would result in a 15 second delay before the method timed out with an error.

To dig further into the issue, I opened the SQL Server Activity Monitor and checked the SQL processes.  Sure enough, there were over 50 processes listed for the eConnect user.

 I then opened Component Services, right clicked on the eConnect 10 COM+ Application and selected Shut Down.  When I looked back in Activity Monitor, all of the eConnect connections were gone.

It has been many years since I studied COM+ connection pooling, so I don't know the exact cause of the problem, but it seems that the eConnect Misc Routines assembly is not properly closing SQL connections and is not reusing existing connections.  It is apparently exhausting all available connections in the pool, eventually leading to a timeout.

My first thought was that I had to stop using the Misc Routines assembly and write my own routine to call the glGetNextJournalEntry stored procedure--something I've done with other integrations that used SQL authentication (since Windows authentication is required for the Misc Routines assembly).

But then I decided to see if there were any settings I could adjust in COM+.   In the eConnect 10 COM+ properties, I reviewed the settings in the Advanced tab and the Pooling & Recycling tab.

I set the idle shutdown to 1 minute, increase the Pool Size from 1 to 2, and to set a Lifetime Limit and Expiration Timeout to try and increase the capacity while recycling the component more frequently.

After making those changes, I am able to generate thousands of journal entry numbers continuously without an error.  But, there is a downside:  increasing the pool size resulted in over 200 idle SQL connections piling up.  Fortunately, once the COM component is idle for 60 seconds, the component shuts down and all of the idle SQL connections are cleared.

Since my JE import only runs once a night, and the client doesn't typically have more than a hundred JEs to process, changing the COM settings is an easier workaround than modifying the import and should not have any negative consequences.


Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com