This article is an update to previous articles about errors on temporary tables when using Microsoft Dynamics GP. We have seen a recent increase in issues of this type as well as a misunderstanding of what is happening to cause the issues.
The issue shows as a dialog with a message like:
An open operation on table ‘MBS_sySecurityView’ failed because the path does not exist
An open operation on table ‘WDC_Field_Security_Tag_TEMP’ failed because the path does not exist
An open operation on table ‘IV_Reconcile_PO_TEMP’ has an incorrect record length.
The name of the table can vary but the examples above include a couple of common ones from products I created, being GP Power Tools and Field Level Security (Advanced Security). However, this issue is due to the Windows environment and has nothing to do with the Dexterity product or SQL Server.
Below is an example of what the dialog can look like:
The Problem
The tables in question are usually not SQL Server tables, but are local c-tree tables stored in the %TEMP% temporary folder of the current workstation. Because of this, looking at SQL Server or capturing DEXSQL logs or SQL Profile traces are all a waste of time as they will not see anything that will help.
The first action needed is to confirm the type of table to understand where the table will be located. If you have GP Power Tools, use the Resource Information window in Table & Fields mode and type in the table name from the error message into the Technical Name field. No need to worry about selecting the product as the Resource Information window will look at all products installed to find the table. Then look at the Type; If it shows c-tree or Memory (c-tree), the file will be stored locally in the current workstation’s %TEMP% temporary folder.
Note: If you don’t have GP Power Tools, you can also go into the Report Writer for the appropriate product and look at the Table resources. If the Database Type shows as c-tree or Memory then the table will be stored locally in the current workstation’s %TEMP% temporary folder.
How c-tree temp tables are created
C-tree temporary tables have a scope limited to the script or form they are being used with.
They are created in the %TEMP% temporary folder and use a name in the format TNTXXXX where XXXX is a random generated hexadecimal number. Initially, Dexterity will choose a number and check that the file TNTXXXX.tmp does not exist. If it does exist, Dexterity will choose a new number and try again.
Once it confirms that the TNTXXXX.tmp file does not exist, it will create the TNTXXXX.tmp placeholder and then create the c-tree data and index files as TNTXXXX.dat and TNTXXXX.idx respectively.
When the temporary table is no longer needed by Dexterity (when the procedure or function that used it completes or when the form using it closes), it will request to Windows to delete the TNTXXXX.dat and TNTXXXX.idx files and then delete the TNTXXXX.tmp file. It does not confirm that the files have been deleted after the requests have been made.
How c-tree memory tables are created
C-tree memory tables have a scope that lasts for the entire session that an instance of the application is running.
They are created in a subfolder in the %TEMP% temporary folder and use a folder name in the format TNTXXXX where XXXX is a random generated hexadecimal number. Initially, Dexterity will choose a number and check that the file TNTXXXX.tmp does not exist. If it does exist, Dexterity will choose a new number and try again.
Once it confirms that the TNTXXXX.tmp file does not exist, it will create the TNTXXXX.tmp placeholder and then create the TNTXXXX folder. The memory tables for that session of the application are created in the sub folder using the name as defined in the Dexterity dictionary with the .dat and .idx extensions.
When the GP application is closed, it will request to Windows to delete the TNTXXXX folder and then delete the TNTXXXX.tmp file. It does not confirm that the files have been deleted after the requests have been made.
Other files in the Temporary folder
Another file that is stored in the %TEMP% folder is the user level Dex.ini file. It is stored as TNT.XXXX.ini when EnablePerUserIni=TRUE is used in the system level Dex.ini file.
What can go wrong
In a perfect world this system will always work and there wouldn’t be any issues. However, the reality is that there are many reasons for issues to occur.
- If the %TEMP% variable does not exist, if the folder pointed to by the %TEMP% variable exists but does not have full control with read and write permissions, or if the drive containing the %TEMP% folder is out of space, you will have issues.
- When GP closes unexpectedly, the TNTXXXX files and folders are left behind.
- Antivirus software can keep a file open and prevent it from being deleted.
- If there are automated processes that delete any of the files while they are in use or deletes some of the files but not others (because they are locked).
- Terminal servers or Virtual Machines set to kill idle sessions are not only risking data damage but will leave temporary files behind.
When the .tmp or .ini files are left behind, they can require Dexterity to take longer to find an available number.
When the .tmp file is removed but the subfolder or .dat and .idx files remains, the TNTXXXX.tmp file can be created again, but the format and/or data in the existing subfolder or .dat and .idx files will not match what is expected and there will be errors opening the file or incorrect record length errors.
The Solution
To ensure that local c-tree tables can be used without issues, please follow each of the steps below:
- Make sure that the temporary folder, as pointed to by the environment variable %TEMP%, exists.
- Make sure that the folder has full read/write access and the drive is not full or running out of space.
- Exclude .dat, .idx, .tmp and .ini file extensions from your Antivirus.
- Ensure that there are no automated tasks removing temp files or folders while users are logged in.
- Delete the contents of the %TEMP% folder immediately after a reboot to clean up (skip files in use). This will remove files left behind after GP has closed unexpectedly.
- Ensure that VM or Terminal Server sessions are not terminated after a period on inactivity. Use GP Power Tools Automatic Logout instead.
GP Power Tools and many other Dexterity products use local Memory and local temporary files. These are not stored in SQL as they get much better performance locally and are only relevant for the current instance of a GP application session.
Advice for Developers: Temporary files should only be setup as Type: Default or SQL, which will be stored in the tempdb SQL database when the table; will contain large amounts of data, will be used on a report, or needs to use specific SQL functionality.
More Information
For more information on Temp Table errors, see the previous articles on the topic here:
Hopefully, this information clears up any confusion and ensures the smooth running of your systems.
David
This article was originally posted on http://www.winthropdc.com/blog.
2 thoughts on “Temp Table Errors – An open operation on table ‘MBS_sySecurityView’ failed because the path does not exist”