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.
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.*/
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.
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:
"General Network error," "Communication link failure," or "A transport-level error" message when an application connects to SQL Server (KB 942861)
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.
Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008 (KB 951037)
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:
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.
The related article by Kayla Schilling from The Resource Group is worth a read:
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.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.