More on SQL Server Connection issues with Microsoft Dynamics GP

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: 

1. Always make sure you close any SQL Server connections you open (regardless of the development tool being used: Dexterity, VBA, or VSTools).
    
2. Never ignore an error dialog (as it means code has not completed executing and the data could be in an unknown state).
    
3. Always report the first error received as any errors that follow are usually the result of that first error.

 

For more information see the following blog posts:

• "The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions
   
• Why does SmartList not return all of the expected data for large queries?
   
• TCP Chimney Setting and SQL Server Error: TCP Provider: An existing connection was forcibly closed by the remote host 
   
• The Importance of Reporting the first Error
   
• The Importance of Reporting Errors
   
• Quick Tip: VBA ADO Connection Run-time error '3709'
   
• Using ADO with VBA with Report Writer

 

I hope you find this information useful. 

David

Comments

• Anonymous
  April 23, 2014
  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.

• Anonymous
  April 28, 2014
  The comment has been removed

• Anonymous
  April 30, 2014
  Posting from John Lowther at Dynamics GP DBA community.dynamics.com/.../david-musgrave-talks-about-vba-and-the-proper-way-to-close-your-connections.aspx

• Anonymous
  June 30, 2014
  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. Thanks -Jeff

• Anonymous
  July 01, 2014
  Hi Jeff 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). David

• Anonymous
  August 21, 2014
  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.

• Anonymous
  August 25, 2014
  Hi Florence It is always good practice to clean up any objects/connections you use. Glad you found this helpful. David