Dynamics GP10 is running on SQL 2005 (9.00.4035.00 SP3). I am the support provider for Dynamics GP10, but they have another service provider for network/hardware. Several users get intermittent errors including:
A SQL network connection error occurred and your connection was cleared. This probably occurred because all available connections to the server are in use. Please try again later.
The stored procedure createSQLTmpTable returned the following results: DBMS: 10054, Microsoft Dynamics GP: 0.
The stored procedure PopulateRMOpenTempTable, Pass Through SQL returned the following results: DBMS: 1054, Microsoft Dynamics GP.
Microsoft SQL Native Client TCP Provider: An existing connection was forcibly closed by the remote host.
A get/change operation on table XXXX failed accessing SQL data. [Several different tables are referenced in the various messages.]
Users (sometimes) report that their GP application slows down dramatically just prior to the error message. This is happening in a wide variety of modules/areas in GP.
We had this issue earlier this year, and I believe the network support team replaced a switch, which resolved the errors for several months. However in the past month, this is happening multiple times daily to several different users in a couple of different areas of the building.
I have researched everything I can find from the Dynamics GP support side. I recommended that the network support team start a case with Microsoft, but they said that Microsoft does not support SQL on 2003 VM, so they cannot submit a case. They tell me that nothing has changed with regards to service packs or anti-virus. Can any one offer suggestions for anything else I can do from the Dynamics GP side or anything else I can point the network support team to? They are having no luck resolving this, the users are going crazy, and we are getting corrupt data in the database. THANKS!!!
I have verified the following:
Maximum number of concurrent connections is set to 0.
The default port is 1433.
REVIEWED/VERIFIED EVERYTHING IN THIS POST - SOME VERIFIED BY NETWORK SUPPORT:
An open operation on table XXX failed because path does not exist
Who hasn't come across this error? If you have been long enough in the Microsoft Dynamics GP world, you will probably even have a prescribed set of steps to address this issue:
1. Restart the SQL Server service to clear all tempdb database tables from memory
2. Clear out the local temp folder
3. Making sure the temp folder actually exists
4. Making sure the %TEMP% environment variable exists
5. Making sure your anti-virus software is not placing some sort of restriction on the folder
And then some more...
Just recently I ran across this issue with Microsoft Dynamics GP 2010 and I thought, how bad can this be? I started by walking through the prescribed steps as mentioned above, but still could not resolve the issue. I also remembered that my friend Allan Cahill over at Developing for Dynamics GP had blogged about this in his article Unexplained Temp Table Errors so I went checking his findings. In his case the issue had to do with the Anti-virus software interfering with the temp directory during scanning for viruses. So, we did the same at the client with no positive outcome.
I then remembered that my also friend David Musgrave had written a more recent article on the issue and in his article he detailed that the same issues could occur if the user lacked write permissions to the folder specified by the %TEMP% environment variable. I went inquiring with the client's IT department and it turned out they had just rolled out some new Group Policies which inadvertly disabled access to the %TEMP% folder.
The client proceeded to make the necessary adjustments via Group Policy and had all users reboot their computers. Now Microsoft Dynamics GP was happy! No more errors!
I ASKED THE NETWORK SUPPORT TEAM TO DO THIS:
.
1. Verify the ODBC that is used to connect to GP. Unless something has changed on the server or workstations that I don’t know about, I don’t think this is the issue, because it was working fine until just recently. But we should verify any way.
a. Launch GP. On the screen with the User ID and Password, the Server is the ODBC connection being used.
b. Check the settings for that ODBC using the attached instructions to be sure it is set up correctly.
2. Verify the TCP/IP settings.
a. Click Start > Run, type in cliconfg and click Run.
b. On the General tab, on the right side under ‘Enabled protocols by order’, be sure TCP/IP is the top one.
c. Highlight TCP/IP and click on Properties.
d. Make sure the default port number is 1433.
3. Go to start > run and type in %temp% on each client machine. Delete the contents of that temp directory.
A SQL network connection error occurred and your connection was cleared. This probably occurred because all available connections to the server are in use. Please try again later.
The stored procedure createSQLTmpTable returned the following results: DBMS: 10054, Microsoft Dynamics GP: 0.
The stored procedure PopulateRMOpenTempTable, Pass Through SQL returned the following results: DBMS: 1054, Microsoft Dynamics GP.
Microsoft SQL Native Client TCP Provider: An existing connection was forcibly closed by the remote host.
A get/change operation on table XXXX failed accessing SQL data. [Several different tables are referenced in the various messages.]
Users (sometimes) report that their GP application slows down dramatically just prior to the error message. This is happening in a wide variety of modules/areas in GP.
We had this issue earlier this year, and I believe the network support team replaced a switch, which resolved the errors for several months. However in the past month, this is happening multiple times daily to several different users in a couple of different areas of the building.
I have researched everything I can find from the Dynamics GP support side. I recommended that the network support team start a case with Microsoft, but they said that Microsoft does not support SQL on 2003 VM, so they cannot submit a case. They tell me that nothing has changed with regards to service packs or anti-virus. Can any one offer suggestions for anything else I can do from the Dynamics GP side or anything else I can point the network support team to? They are having no luck resolving this, the users are going crazy, and we are getting corrupt data in the database. THANKS!!!
I have verified the following:
Maximum number of concurrent connections is set to 0.
The default port is 1433.
REVIEWED/VERIFIED EVERYTHING IN THIS POST - SOME VERIFIED BY NETWORK SUPPORT:
An open operation on table XXX failed because path does not exist
Who hasn't come across this error? If you have been long enough in the Microsoft Dynamics GP world, you will probably even have a prescribed set of steps to address this issue:
1. Restart the SQL Server service to clear all tempdb database tables from memory
2. Clear out the local temp folder
3. Making sure the temp folder actually exists
4. Making sure the %TEMP% environment variable exists
5. Making sure your anti-virus software is not placing some sort of restriction on the folder
And then some more...
Just recently I ran across this issue with Microsoft Dynamics GP 2010 and I thought, how bad can this be? I started by walking through the prescribed steps as mentioned above, but still could not resolve the issue. I also remembered that my friend Allan Cahill over at Developing for Dynamics GP had blogged about this in his article Unexplained Temp Table Errors so I went checking his findings. In his case the issue had to do with the Anti-virus software interfering with the temp directory during scanning for viruses. So, we did the same at the client with no positive outcome.
I then remembered that my also friend David Musgrave had written a more recent article on the issue and in his article he detailed that the same issues could occur if the user lacked write permissions to the folder specified by the %TEMP% environment variable. I went inquiring with the client's IT department and it turned out they had just rolled out some new Group Policies which inadvertly disabled access to the %TEMP% folder.
The client proceeded to make the necessary adjustments via Group Policy and had all users reboot their computers. Now Microsoft Dynamics GP was happy! No more errors!
I ASKED THE NETWORK SUPPORT TEAM TO DO THIS:
.
1. Verify the ODBC that is used to connect to GP. Unless something has changed on the server or workstations that I don’t know about, I don’t think this is the issue, because it was working fine until just recently. But we should verify any way.
a. Launch GP. On the screen with the User ID and Password, the Server is the ODBC connection being used.
b. Check the settings for that ODBC using the attached instructions to be sure it is set up correctly.
2. Verify the TCP/IP settings.
a. Click Start > Run, type in cliconfg and click Run.
b. On the General tab, on the right side under ‘Enabled protocols by order’, be sure TCP/IP is the top one.
c. Highlight TCP/IP and click on Properties.
d. Make sure the default port number is 1433.
3. Go to start > run and type in %temp% on each client machine. Delete the contents of that temp directory.
I am also running a DexSQLLog. I think I will just let both of them run for the next couple of work days and see if she can get an error that I can capture. Thanks again!