Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
You might have heard me discuss the TCP Chimney settings before. If not, don't worry as this post is here to make sure you know about the setting and make sure it is disabled for all your Microsoft Dynamics GP installs.
My earlier post which mentions the TCP Chimney settings, discusses an issue with a large SmartList query not returning all the expected data. This post aims to discuss the bigger picture without referencing a specific module or function.
The Problem
When you login to Microsoft Dynamics GP, a connection to the SQL Server is created which has a SPID (Server Process ID) associated with it. As you work with Dynamics, it uses this connection to continuously communicate to the SQL Server. When Dynamics creates SQL temporary tables, they are created as ##tables and linked to the current SPID. The tables exist until the Dynamics code finishes with the table and closes it, or until the connection for the SPID is broken and SQL "cleans up" and removes the table.
If the connection is lost for any reason, Dynamics will re-establish a new connection with a new SPID so you can continue working. However, any queries currently in process will stop as they were for the old SPID. Also temporary tables created for the now disconnected SPID are not available to the new SPID as they were destroyed by SQL when the old SPID was lost. The fact that Dynamics automatically reconnects could make it appear as though nothing has happened, so the end user might not realise they had been disconnected.
Losing the connection can cause:
The stored procedure createSQLTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.
The stored procedure createTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.
Note: This issue is most common on windows that use ##temp tables to populate scrolling windows. Two primary examples of windows which behave this way are the RM and PM Inquiry windows (see article in More Information section below).
/* Date: 06/20/2013 Time: 1:43:08
SQLSTATE:(08S01) Native Err:(10054) stmt(21931584):*/
[Microsoft][SQL Server Native Client 10.0]TCP Provider: An existing connection was forcibly closed by the remote host.
*/
Possible Causes
There can be a number of reasons for the SQL Connection to be lost between the SQL Server and a client workstations, including but not limited to:
But there are also a couple of settings which are known to cause disconnections. These are the TCP Chimney setting and the SyncAttackProtect setting. Disabling these settings on both the Server and the client has been known to resolve unexplained disconnections.
The Solution
Below are the steps from my previous "SmartList" article with the steps to disable the features and the links to the relevant KB articles:
The following article explains the issues with the TCP Chimney primarily for a Windows Server 2003 machine:
The following registry changes will disable the TCP Chimney feature on a Windows Server 2003 machine:
The following article explains how to disable the TCP Chimney for a Windows Server 2008 machine.
The following command line command will disable the TCP Chimney feature on a Windows Server 2008 or later machine:
netsh int tcp set global chimney=disabled
Troubleshooting: Connection Forcibly Closed (TechNet Article)
The following registry changes will disable SynAttackProtect feature:
More Information
For more information on this and related issues, see the following articles:
I hope the helps resolve some of those unexpected and weird errors for you.
David
Anonymous
February 25, 2014
The related article by Kayla Schilling from The Resource Group is worth a read:
dynamicsuniversity.com/.../fp-couldnt-close-table-one-setting-may-end-random-disconnects-your-gp-databases
Anonymous
October 07, 2014
Sir,
I tried your solution above but I am still having problems with my sql connection. What is confusing is that I have set up a new server, restored a backup copy of the database and until now, I am NOT having any problems. But with the old servers, I am having this TCP errors. As I remember, these error started showing when I executed a code that have sql queries inside a loop. At first, the code executed completely but having ran it for several times, the errors started to show and now, even a simple query that returns a hundred or so records causes these tcp errors.
Sometimes, this simple query executes but most of the time, an error occurs. Please help, I have these problems for a long time that even our DB admin is clueless how to resolve the problem.
Thanks.
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in