I have been working on a recent support case that had a situation that makes me want to raise this topic again.
In the past, I have posted a number of articles discussing reasons for connections to SQL Server to be lost. When Microsoft Dynamics GP loses its connection to SQL Server, the Dexterity Runtime automatically creates a new connection to minimise disruption. However, this reconnection has a new SPID and is not linked in any way to the previous connection.
This means that any SQL temporary tables created in the original connection will not be available in the new connection. When Microsoft Dynamics GP attempts to access or close the temporary tables it believes exists, you will get errors.
Below are some example errors (all screenshots from the same case):
A remove range operation on table 'XXX' cannot find the table.
A save operation on table 'XXX' cannot find the table.
FP: Couldn't close table!
It is important to note that once a Dexterity Script generates an error, the script will abort and whatever tasks were later in the script will not be completed.
Sometimes these table errors can cause other errors to be generated.
All call stacks are in use. Cannot start script.
Please note that ignoring any error in Microsoft Dynamics GP is a risk (see my blog articles on the Importance of Reporting Errors below).
What stood out as different with this case was the following error dialog, which I had never seen before:
A SQL network connection error occurred and your connection was cleared. This probably occurred because all available connections to the server are in use. Please try again later.
This error message made me wonder what was happening with the connections to the SQL Server. The customer's System Administrator obtained graphs of the connections, below is an example showing how the connections keep increasing during the day until the server forcibly closes connections.
Connections increasing to a maximum of 2303 connections during half a day
So, what could cause Microsoft Dynamics GP to continuously use more and more connections?
I had one idea about what could be the cause, so I asked the question to the partner: "Does the site have VBA code that uses ADO to access SQL Server data?"
The answer was "Yes".
So then I asked about how the ADO (ActiveX Data Objects) connection was being closed at the end of the VBA (Visual Basic for Applications) code (when exiting the form or report). We looked at the VBA project and the code used to clean up looked something like:
' Close ADO Connection Set cn = Nothing Set rst = Nothing Set cmd = Nothing
When the code should look something like the following example:
' Close ADO Connection If rst.State = adStateOpen Then rst.Close If cn.State = adStateOpen Then cn.Close Set cn = Nothing Set rst = Nothing Set cmd = Nothing
The problem is that code was destroying the connection object "cn" before actually closing the connection, thus leaving the connection to the SQL Server open with no way to close it anymore.
Best practice for VBA used in reports and forms when using ADO is to close the result set and connection before destroying the objects. Please see the blog posts below for details of the best practice methods for using VBA with ADO on Forms and Reports.
After a code review and making the changes needed, the connections on the customer's system levelled out, with a maximum of 539 connections.
Connections rising a small amount as users log in, but staying fairly constant throughout the day
So the lessons learned here are:
For more information see the following blog posts:
I hope you find this information useful.
I ran into this issue the first time I coded for GP. In many languages, destroying an object initiates cleanup routines like a destructor or finalizer. It was easy to (incorrectly) assume that destroying the connection object in VBA would also close it.
It is easy to blame the VBA coder, BUT...
This problem reared its ugly head in my environment only AFTER the upgrade to GP2010. I did not experience this problem in 8.0 or 10.0. I'm not sure what changed, but I had to re-write a lot of VBA in hurry. The previous versions seemed to be closing the connections when the window closed. GP2010 closes the connections only when the application closes (or the VBA developer does it in code).
Posting from John Lowther at Dynamics GP DBA
Curious...I'm having very similar issues with many of the same error messages you've posted.
Please excuse the 2 rookie questions that (hopefully) don't fall under the category of "technical", but
1. Where to I go to see if the close connection has the correct code?
2. How do I get GP to print out the total connections graphs you've displayed.
You would need to look in the Visual Basic Editor (under Tools >> Customize menu), and check that any ADO connections opened are closed before being destroyed.
You can use Performance Monitor with the Logical Connections counter on the MSSQL$SQL2012:General Statistics object (or similar depending on your version or instance).
Read many ADO tutorials and articles and never seen anyone closing connections so I assumed setting to nothing was doing it. I'm glad you pointed out it was not. I remembered in other languages, i was closing it manually. Will improve my VBA. I don't like that language very much but I'm trying to do my job as professionnaly as possible! :)
Thanks for sharing, it was really helpful and instructive article.
It is always good practice to clean up any objects/connections you use.
Glad you found this helpful.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.