How to Fix Null Characters in NOTE Table
Version: 8.2 & Up
If you receive the following error from our JSON web services --
[Exception]:[System.Data.ConstraintException: The 'NOTE_ESTIMATED_TIME' property on 'NOTES' could not be set to a 'null' value. You must set this property to a non-null value of type 'System.Int32'.
-- it is because there are Null characters in the Elliott Note table (in this case on a numeric field) and the web service can't convert the numeric field with Null characters; hence the exception.
The bigger question is why would we have Null characters in Elliott's Note table? The likely scenario is that you have a third-party shipping manifest system that writes tracking numbers to the Elliott Note table, and the way in which the manifest system is written is causing the Null characters in the Elliott Note table. Please refer to the following KB article for more details:
Even though you can work with your third-party manifest system to stop them from inserting Null characters into Note tables in the future, the question is how do you get rid of those records that are already in the Note table that contains Null characters?
We have developed a NOTEFIX utility for this purpose and you can download it with the following URL:
After you download this utility, unzip it. You will see a single file NOTEFIX.INT. Please right click on this file in Windows Explorer and choose "Properties." If you see this warning:
"This file came from anothercomputer and might be blocked tohelp protect this computer."
(see sample screen below)
you must choose to "unblock" it.
Then you should copy this file to the Elliott startup folder:
For Elliott 8.2 <ElliottRoot>\BinFor Elliott 8.5 <ElliottRoot>\Bin85 or <RunLocalPath>\Bin85
For Elliott 8.5, chances are you are running from a local path. Right click on your Elliott 8.5 desktop icon and find that local path, and copy the NOTEFIX.INT file to that folder. See the following sample for run local path:
Now start up Elliott and login as Elliott user SUPERVISOR. On the Elliott Control Center, click on the menu File, and choose "Run Special." See sample screen below:
You will not see the "Run Special..." option if you not logging into Elliott as user "SUPERVISOR".
The following window will show up:
Enter "NOTEFIX" in the Program Name. Under the Module Library, choose "UTL," then click OK. If you get error 173 on NOTEFIX, it means you did not copy the NOTEFIX.INT into the folder from which you run Elliott.
If the system is able to find the NOTEFIX.INT program, then you will see a progress window showing the note record it is fixing. At the end, you will see a confirmation window of how many note records were fixed.
Click OK and exit Elliott. Now you are done!
EMK