There are times when the Microsoft Dynamics GP Support team receives SQL connection/network cases, and I therefore wanted to get the information that we have in regards to this issue 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. Windows Firewall is completely disabled or else has a rule so that port 1433 is not being blocked, as this is the port number SQL and Dynamics GP use by default.

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 exceptions within any antivirus applications for the following items:

--- 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
--- C:\Windows\Temp folder
--- Users local temp folder

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 2008 and Windows 7:

        951037Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008
            http://support.microsoft.com/kb/951037/EN-US

  b. How to Disable TCP Chimney in Windows 2003 Server and Windows Vista

         942861"General Network error," "Communication link failure," or "A transport-level error" message when an application connects to SQL Server:
            http://support.microsoft.com/kb/942861/EN-US

  c. 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.

https://mbs.microsoft.com/customersource/northamerica/GP/learning/documentation/white-papers/MDGP2010_WhitePaper_Performance

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.

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!