More on SQL Server Connection issues with Microsoft Dynamics GP

  • Article

David Meego - Click for blog homepageI 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:

 

I hope you find this information useful. 

David