Hello Deanne,
My name is Brandon and I am on the Dynamics GP Team at Microsoft. I would like to provide a response your question. Please see below.
The error message you’ve described is what we see when there are disconnects. GP and SQL are in constant communication with each other, so if GP sends 4 packets of information but SQL only receives 3, GP will return an error.
We use the following steps to reassure the issue is network related. Here are the steps:
1. Go to one of the client workstations or terminal server.
a. Install SQL Server Management Tools on the client machine
b. Login to SQL Server Management Studio and connect to your SQL Server
c. Create a new query against TWO or a test database
d. Run the following query: select * into ##GL00100 from GL00100
e. Now just let SQL Srv {Namepii} Studio sit open on the machine, don’t do anything else with it (you can minimize it). You want to leave this open until you believe you’ve had a network interruption that has caused an error in another program.
f. You can periodically run the following script, or run it after you think you’ve had a drop: select * from ##GL00100 g. If you get an error like “Invalid Object Name ##GL00100” then you have lost your connection to SQL Server.
g. The query connection you open with SQL Server through mgmt. studio should remain open FOREVER unless you actually close it out.
We are having you create a temp object because SQL will not get rid of the temp object unless it’s told to via a drop table command or a network disconnect occurs between you and SQL Server.
Below are some common troubleshooting steps that can prevent some network connectivity issues:
1. In SQL Server Management Studio, right-click on your SQL instance name and choose Properties. In the 'Server Properties' window, click on the 'Connections' page.
a. 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.
b. Ensure that within any Antivirus installed the following is found in the exclusions from scanning: GP code folder. Any network shares containing shared reports and forms dictionaries. The user’s temp directory.
c. Next, setup and use a new ODBC System DSN connection for Dynamics GP 2010 using the steps in the following KB article: a. mbs.microsoft.com/.../MDGP_HOWTO_SETUP_ODBC
d. Once you setup the new System DSN connection, launch Dynamics GP on that machine and select the new ODBC connection, then verify whether you still see these random errors show or if it seems to resolve the issue.
2. Has the SQL Server been rebooted recently?
a. Start by restarting the SQL services, then if that doesn’t change anything, restart the entire SQL Server.
b. Run the following scripts in SQL
i. SELECT * FROM DYNAMICS..ACTIVITY
ii. SELECT * FROM DYNAMICS..SY00800
iii. SELECT * FROM DYNAMICS..SY00801
iv. SELECT * FROM TEMPDB..{NAMEPII}_LOCK
v. SELECT * FROM TEMPDB..{NAMEPII}_SESSION
c. Disable TCP Chimney on the server and workstations.
i. How to Disable TCP Chimney in Windows Server 2008 and Windows 7: support.microsoft.com/.../951037
ii. How to Disable TCP Chimney in Windows 2003 Server and Windows Vista support.microsoft.com/.../942861
d. Make sure the (Network Card) is not set to sleep.
i. Open Device Manager Right Click your Network Card.
ii. Properties Under the advanced tab
iii. disable anything that has to do with power saving.
I hope this helps!
Thank you!
