SQL Connectivity Errors seen in Microsoft Dynamics GP

There are times when the Microsoft Dynamics GP Support team receives SQL connection/network cases, so, we wanted to get the information that we have in regards to these type of issues, published in a blog. So here goes…

Microsoft Dynamics GP requires a constant connection with the SQL Server holding the databases for the GP application. SQL Server's connection should never be dropping unless the SQL Server service is manually stopped or the server itself is restarted.  With Microsoft Dynamics GP, it isn't always obvious when you are facing a disconnect to the SQL Server.

There are certain errors that may render within Microsoft Dynamics GP, which can point to a SQL reset occurring:

-- FP: Couldn't Close Table!

-- Invalid Object Name..

-- Couldn't find stored procedure...

-- Timeout period has expired.

-- Cannot find the table...

--A remove range operation on table 'syContentPageXMLCache' cannot find the table

To verify whether a SQL connection is being dropped, you can use the following options:

>> ##GL00100 temporary table

    1. On a machine experiencing the SQL connection issues, install SQL Server Management Studio.

    2. Against a Dynamics GP company database, run the following script to create the temporary table:

            Select * into ##GL00100 from GL00100

    3. Periodically, or when you believe a SQL connection was dropped, run the following script to verify the table still exists in the same company database:

            Select * from ##GL00100

    4. The ##GL00100 table should still exist until we manually delete it or SQL Server is restarted or rebooted. If we see this temp table deleted otherwise, it means the SQL connection dropped, which removes all temporary objects, which begin with ## in Microsoft Dynamics GP.

>> Continuous Ping to the SQL Server from the problem machine

   1. Open a Command Prompt window on the problem machine using 'Run As Administrator'.

   2. Type the following command in the Command Prompt window, replacing SERVERNAME with the name of the SQL database server:

             ping SERVERNAME -t -l 65500

   3. Let the ping run for a period of time or until you experience the SQL network issue. What we're looking for is 'Request Timed Out' or 'Transport-Level Error' messages indicating the connection to the server wasn't stable.

>>Another option is to run a Network Monitor trace when experiencing the issue and see if there are any resets in the TCP acknowledgements. The description of the packet will show as either an R or RST.

To verify and troubleshoot these SQL connection issues with Microsoft Dynamics GP or if you're unable to get an ODBC DSN to connect to SQL Server at all, you can look at the following potential causes:

1. The Windows Firewall and/or any additional firewalls are not blocking traffic between Microsoft Dynamics GP and SQL Server, such as having a rule so that port 1433 is not being blocked, as this is the port number SQL and Dynamics GP use by default.  ***NOTE: As a test, you can try disabling the firewalls on the Dynamics GP and SQL Server machines, but this is only recommended during testing, and not a fix for this type of issue, due to it being an obvious security risk***

2. On the SQL Server, under Administrative Tools > Services, make sure the SQL Browser service is started/running and is set to 'Automatic'.

3. Make sure the ODBC DSN on each machine that Microsoft Dynamics GP is using, is setup as per KB 870416 and that the SQL Server name and name of the ODBC DSN is identical on each machine. https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/how-to-articles/MDGP_HOWTO_SETUP_ODBC

4. Can you ping the SQL Server from the workstation(s) showing the network errors?

    If so, setup the ODBC DSN on the workstation(s) using the IP address of the SQL Server instead of the server name. Do you still get the connection errors or does this seem to resolve those?

    If it resolves the network errors, it would seem to show there may be a DSN issue with your network and you would need to look at that further.

5. If any machines experiencing SQL connection issues is using wireless, connect them with a network cable to the internal network and test again.

6. On the SQL Server, in the SQL Server Configuration Manager, verify TCP/IP and Named Pipes are showing as enabled for the server protocols and under TCP/IP, that the port number being used is 1433 or something different. If it is not 1433, you may need to setup the Windows Firewall on the non-working workstation to not block that specific port number instead.

     a. SQL Server Services - again, make sure the SQL Server service and SQL Server Browser service are showing as 'Running'.
Enabled

     b. SQL Native Client ## Configuration - Client Protocols - verify that TCP/IP, Named Pipes and Shared Memory are all showing as 'Enabled'.
Enabled

     c. SQL Server Network Configuration - Protocols for <SQL Server> - again, verify that TCP/IP, Named Pipes and Shared Memory are all showing as 'Enabled'.

7. On the workstations and secondary servers, click on Start > Run, type in 'cliconfg' and click OK to open the SQL Server Client Network Utility. Verify that both TCP/IP and Named Pipes are showing as enabled, and TCP/IP is above Named Pipes.

    Also, on the 'alias' tab, make sure nothing is showing here, if it is, delete it and try the connection again.

8. Create exclusions within any antivirus applications for the following items, as we see these type of issues with Microsoft Dynamics GP is being actively scanned by antivirus and related applications:

--- Dynamics GP install folder
--- Dynamics.exe file
--- *.idx file extensions
--- *.cnk file extensions
--- *.dic file extensions
--- *.chm file extensions
--- *.set file extensions
--- *.ini file extensions
--- *.vba file extensions
--- *.log file extenstions
--- *.dat file extensions
--- *.tnt file extensions

---Any shared directories or folders, holding forms and/or reports dictionary files.

9. In SQL Server Management Studio, right-click on your SQL instance name and choose Properties.
    a. In the 'Server Properties' window, click on the 'Connections' page.
    b. Here, verify that the 'Maximum number of concurrent connections' is set to 0 (zero) which is for unlimited connections. This is the default setting for SQL Server.

10. Make sure the NIC (Network Card) is not set to sleep.
    a. Open Device Manager
    b. Right Click your NIC, Properties
    c. Under the advanced tab, disable anything that has to do with power saving.

11. Disable TCP Chimney on the server and workstation(s).

   a. How to Disable TCP Chimney in Windows Server 2012 R2 and Windows 10:

        Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008 - Windows Server | Microsoft Docs

  b. In newer server operating systems, go to Control Panel > System > Device Manager, under Network Adapters, right-click on the adapter in use, choose Properties, then in the Properties window, click the Advanced tab where all features related to TCP Chimney offload on the network card are listed.

12. Clear the SQL temp files

   A. Have all users log out of Dynamics GP, make a backup of the databases, then stop and restart the SQL Server service which will remove all temp objects from the SQL and GP databases.

         ***Note: Do not simply restart the server, as rebooting the entire server doesn't always clear out the SQL temp tables.

   B. Clear user data in GP tables:

        1. Open SQL Server Management Studio and log in as ‘sa’

        2. Run the following scripts in a ‘New Query’

           a. Use DYNAMICS
               Delete SY00800
               Delete SY00801
               Delete ACTIVITY

          b. Use TEMPDB
               Delete DEX_LOCK
               Delete DEX_SESSION

    C. Log back into Dynamics GP and check if the network errors still occur

Another good reference is the Microsoft Dynamics Performance white paper, which has a number of networking guidelines/recommendations to prevent network disconnects as well as both network and SQL Server optimization steps and maintenance steps.

If you've gone through all of the above and you still are seeing SQL connection issues and errors occur with Microsoft Dynamics GP, then the next recommendation would be to rule out any hardware that may be in place, such as network cards and cables, any routers or switches that are being used in this network environment with SQL Server.

There is also a SQL Script you can run to give you a list of errors that were recorded by SQL.

Run this script in SQL Server where your GP databases are held, and it will give you a list of errors recorded by SQL, the date/time, error number and text of the error, along with SPID, remote host and port information and more.

Once you get the error number and information from the above script, you can then look at the SQL logs under Management > SQL Server Logs, find the log from the date/time the last connection issue was observed and then look at the corresponding log file for that date/time.

Hopefully this will resolve some of these types of SQL network errors and issues with Microsoft Dynamics GP or at least give you a head-start on troubleshooting.

Thank You!