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

Don't forget your keys (and the taxable wages)!

I have had a few support issues in the past couple weeks regarding the taxable wages field on the payroll manual checks window.  Here is a few scenarios that come up, most often when beginning balances have been entered previously:

1.  Federal taxes are correct, but Federal wages are not
2.  FICA/S and/or FICA/M is correct, but the associated wages are not
3.  Federal wages are off from FICA/M or FICA/S wages, but you can't identify why

How does this happen?

• Beginning balances are entered using Transactions>>Payroll>>Manual Checks, select Beginning Balances
• Transactions entered by employee for the different transaction types related to taxes (Federal Tax, State Tax, FICA Social Security, FICA Medicare, etc)
• Tax amount entered properly
• Taxable Wage field (appears below the Amount field where you enter the Tax Amount) was not entered properly
• The result is that taxes are correct, and gross wages are correct, but the taxable wages (Fed Wages, State Wages, FICA/M Wages, etc) are not.

Another scenario that can contribute to the issues above is when an individual has wages that are subject to tax, but did not have tax withheld.  Logically, when entering beginning balances, you might just omit the Federal Tax transaction.  While that is correct in terms of not recording tax withheld, it will also cause the wages to not be recorded as taxable wages (regardless of how the pay code is set up).  So, in this scenario, you should enter the transaction for Federal Tax (or State Tax or FICA Medicare ...).  But the Amount should be zero, and the Taxable Wage should be populated with the Federal Wage amount.  This will result in no impact to the net wage (since no tax was withheld), but the wages will be reflected in Federal Wages.

What else does this emphasize?  It is very important that beginning balances be reconciled by printing the 941 report for the periods covered by the beginning balances and also by reviewing the employee summary windows.  I have even suggested that users build the year end wage file early (you can then remove it under Tools>>Utilities>>Payroll>>Remove Year End) to see if the W-2s look correct.  This reconciliation often takes a back seat, we think that we can deal with it at year end.  And although that is technically true, the earlier you reconcile and resolve errors, the less stress you will add to your first year end process!

Oh, and before I leave, how do you fix the scenarios above?  It is a pretty easy, albeit tedious, process.  Simply enter another beginning balance transaction (Transactions>>Payroll>>Manual Checks). Enter a transaction for each of the taxes, leaving the amount field blank but populating the taxable wages as needed.  Make sure you use the proper dates when entering the beginning balances, and that you only populate the taxable wages field and not the actual amount field used to record the tax amount.

Happy Year End!

Popular Payroll Year End Questions

Courtesy of a fabulous webinar by Microsoft and Terry Heley, here is the bottom line scoop on many of the questions that have been flying around with the change in the FICA rates:

Q.  What do I do if I already applied the 2011 tax tables BEFORE I built the year end wage file?
A.  Easy fix.  Manually edit the FICA/Social Security tax table (Tools>>Setup>>System>>Payroll Tax) to set the rate back to 6.2.  Build the year end wage file (Tools>>Routines>>Payroll).  Then set the rate back to 4.2% (the new rate for 2011)

Q.  Following the method above, can I rebuild the year end wage file even if I have processed payrolls in 2011?
A.  Yes, yes you can! Just make sure to change the FICA/Social Security rate in the tax tables before, and then reset it after.

Q.  Can I print my 941 for 2010 even if I have applied the tax tables for 2011?
A.  Yes, prior to the release of the update from Microsoft (scheduled for 1/24), you can simply change the tax tables as noted above and set your user date back to 2010 to print the 941.  Once the update has been released, the report will be date sensitive so you can print it for either 2010 or 2011 (based on your user date) without changing the tax tables.

Q.  What manual adjustments do I need to make prior to the release of the update from Microsoft?
A.  You only need to consider (2) things.  First, that you adjust your tax deposit to the government to include an additional 2%.  Second, you need to adjust the FICA employer expense and liability in the General Ledger for the same amount (for more information on this, refer to Mike Lupro's excellent post on this topic).

Good luck in finishing up your year end!

Take care,
Christina

Apologies, apologies, and more apologies

So, I know I have been so very absent from this blog lately and I am sure you all have been missing my witty posts :)  But the fact is I have been going through many many changes lately.  Change is fun! That is my new mantra.  First, Aubrey turned 1 year old on January 10th.  She is walking and even trying to run, and keeps us on our toes.  Second, after almost 10 years, I have left The Knaster Technology Group and am getting settled in at BKD Technologies in Kansas City.  It is an exciting change for me, with an office to go to everyday, less travel, and a variety of new opportunities to keep me on my toes when Aubrey isn't :)  Although there is always a little bit of sadness in change, and this one is no exception.  The Knaster Technology Group is a great place with great people.

I promise I will get back on schedule in the coming weeks, and will get back to my less-technical but infinitely more helpful posts than Steve's--just trying to incite a little healthy competition with my blogmate :)

Happy new year and happy year end to all!

Take care,
Christina

Remote Desktop: Cannot copy file: Cannot read from source file or disk

Back in February I wrote about some insight I gained while trying to finally troubleshoot why copy and paste would sometimes not work between a workstation and an RDP session.

Well, I recently started running into this error message while trying to copy a file from my workstation to an RDP session:

Cannot copy file: Cannot read from source file or disk

I tried killing and restarting rdpclip, but that didn't help.  I then double checked that my local drives were mapped to the RDP session, which they were.  And I verified that the Clipboard was enabled in my RDP connection.  I even went back to my blog post and re-read my own tips and the Microsoft tips, but I seem to have covered everything, including restarting the remote server.  But I would still occasionally get the error.

After several minutes of trying to figure out the cause of the error, it finally dawned on me.

I use TrueCrypt on my workstation to encrypt sensitive files, such as employee or payroll source data files that are required for integrations.  When you mount a TrueCrypt volume, it becomes a new drive on your machine--in my case, it happened to be the S: drive.

So, the problem was that I was first connecting to my RDP session, which did map all of my local drives.  But I then later mounted my TrueCrypt volume, which created my S: drive.  Naturally, the RDP session didn't have an S: drive mapped.

Since I was trying to copy a file from my local S: drive to the RDP session, and since my S: drive was not mapped via RDP, the file copy would fail with the error message.  Obviously this isn't an issue if your drive letters are fairly static, but since I don't regularly keep my TrueCrypt volumes mounted and only mount them when necessary, the S: drive isn't a regular actor on my machine.

One simple, but slightly annoying workaround is to copy the files from the unmapped drive to a mapped drive on the local workstation (such as copying to your desktop, which would put the files on your C: drive), and then copying from that drive to the RDP session.

Alternately, if your RDP connection is setup to map the drive once it is created, disconnecting and then reconnecting to the RDP session should work.


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

Integration Manager 2010 Error: Could not load file or assembly Interop.MSScriptControl

I recently fielded a question on Experts Exchange where a user was receiving an error trying to run an integration in Integration Manager 2010:

Could not load file or assembly 'Interop.MSScriptControl, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.

The same integration worked okay on Integration Manager 10, and it also works fine with Integration Manager 2010 on a different computer.


I wasn't able to find anything in the Knowledge Base or Google about this specific error, so I had to dig around.  At first I got distracted by some references to MSScript.ocx, but it turns out that this error is completely unrelated to the OCX file, as it relates to the Interop.MSScriptControl.dll file, which is normally installed in the Integration Manager 2010 application directory.


Once I found this file on my server, I felt that I was getting closer, but I still wasn't sure why the error was occurring on the client's computer.  I first checked to see if there were any references to the file in the registry, but there were none.  I then checked the .NET 1.1 and 2.0 Global Assembly Cache lists, but didn't see any reference to the file there either.  Given this, my only guess was that the file was probably missing from the Integration Manager application directory.


I asked the user to make sure the file was present in the Integration Manager application directory, and sure enough, it was missing.  Once she copied the file from another GP 2010 machine, the integration ran fine.


She decided to check all of the other Integration Manager files and found that the Interop.ADOX.dll file was also missing, so she copied that one over as well.


I'm assuming this is a rare, random issue where the files were not installed properly, but in case anyone else runs into it, fortunately there is a simple fix!


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

Payroll FICA Fun!

Sharing Michael Lupro's recent post on the FICA changes (different rates for employee -vs- employer, oh  my!) and the adjustments you will need to make in GP until the update is released to address different employer -vs- employee rates.

http://btosteam.blogspot.com/2011/01/manual-employer-fica-tax-calculations.html