[Joshua Crooks]

I have a very funny problem with a recent site that I upgraded from DOS/Windows 7.1 to 7.3. The problem is occurring in the Service Manager.

At the clients request I changed the Transaction Number sequence of the Job Cards from C3500 to just 3500. After performing the above step the following started occurring:

After adding a new job and filling in all details in the relevant fields (which included custom fields) and saving the job, all information sitting in the custom fields on the screen disappears.

If I restart Capital and go back to that specific job the information is still missing. Now if I type that same information in the fields and save again it seems to work fine.

After changing the Transaction Number back to have a “C” in front of it everything seems to work fine again. The customer is content with this for now, but they would still like to remove the “C” from the Transaction Number if possible.

Your thoughts are appreciated.

[COBS Tech Support]

User field data is stored in the table SMAUX000.DBF and standard field data in the table SMJOBS.DBF

The information is connected together through a field which holds the job number in SMAUX000.DBF. When you change the job number in SMJOBS.DBF, CAPITAL should find the matching record in SMAUX000.DBF and update it also, so the information stays connected.

If the information "disappears" then the most probable reason is that the file SMAUX000.CDX is damaged. The CDX acts as a "look up" table to connect the two tables together. Running an Automatic Repair or Database Maintenance should fix the problem, because this utility recreates all .CDX (compound index) files.

If that doesn't solve the problem it may be because there is some problem in either the contents or the structure of either of these tables. By the sound of the problem you describe it might be something illegal in the structure of SMAUX000.DBF. (This usually happens when users open the tables inside Excel and accidentally alter the table's structure by resizing columns and saving the data back into the database. A bad thing to do at the best of times.)

There is a utility that will conform all data to the correct structures which you can run by executing:

UPGRADE /RESTORE

from the command console.

This utility can take a very long time to run and if the table structure is badly damaged, doing the above may make all problems immediately "obvious" to users. It's always a good idea to do a complete back-up before doing an UPGRADE /RESTORE

A third but much less likely possibility is that there is illegal character data within the "key fields" of the database, causing the connecting link to be broken. In this case there is not much that can be done short of deleting the offending record and re-keying the data into a new clean record.

[Joshua Crooks]

No problems. I have tried the basics, i.e. Automatic Repairs and Database Maintenance with no luck so I will give the UPGRADE /RESTORE a try and see what happens. Just out of curiosity when I run a normal upgrade from an older version (like I did with this site) does that go as far as checking table structures for damage or corruption or problems in the .CDX files? If not should I be running this /RESTORE command when I do an upgrade?

[COBS Tech Support]

It depends on the version the user is running and the version the user is upgrading too. If there have been upgrades to the service manager tables, and the invoicing tables (for example), then these tables will be opened, and their contents copied into new clean versions of those tables. When the new tables are verified as having been created OK, the old tables are deleted and the new tables are renamed and replace them. So there could be table damage that might still exist in the system after an upgrade, because various tables were never touched during the upgrade process.

.CDX files are potentially fragile because they are updated heavily, and for any file that is heavily updated, the chances of its corruption greatly increases. (This does not apply to the Corporate Edition of CAPITAL which has a different form of communication with client applications (SQL) that prevents this type of corruption from occurring.) For this reason UPGRADE does not use existing CDX files and if it needs any look-ups, it will build it own temporary indexes and then throw them away afterwards.

You should never need to "normally" use UPGRADE /RESTORE. A few cases where it may be needed is when:

* Table structures have been corrupted

* Tables have been illegally modified by end-users in Excel or other applications capable of modifying tables.

* Tables have been mixed up because users have been copying different versions of different tables between computers or sites. E.g., the user may take a table home to work on, then copy it back onto this machine at the office the next day. Users should never do this. If they do want to work on a copy at home they should make a complete back-up of their database and move that only.

Needless to say any of the above may have happened a long time ago. The problem may start to get noticed after upgrading to a newer version for various reasons.

[Joshua Crooks]

Executing UPGRADE /RESTORE didn't help in this case. Could this issue be caused by illegal characters in the database caused by data corruption and does this mean I'm going to have to manually inspect them one by one?

[COBS Tech Support]

While it could be due to illegal characters in the database this is not very likely, especially if you don't see any other corruption visible in the job cards.

Unfortunately it could also be the case that at some point in the past, STAUX000.CDX (user fields for job cards) got damaged so that some of the auxiliary records could not be "seen" by the system. If the system can't see those records, it will create new ones.

When an automatic repair or database maintenance is eventually executed, those records become viewable again, including the ones that were created twice. Now there are duplicates.

When the system goes to change the job number, it changes one of those records but not both (because there should only be one record in STAUX000.DBF for each record in SMJOBS.DBF the system won't look for duplicates because it isn't expecting to find any.)

Another possibility is that it wasn't that STAUX000.CDX was damaged but that a different version of STAUX000.DBF was copied into the database in the past, knocking everything out of sync. (This happens sometimes when a table gets destroyed due to some kind of OS or hardware malfunction--and the IT person or the end-user just restores the damaged file from their back-up rather than restoring the entire back-up of the company data. Years can pass before the problem gets noticed. Mixing different files with different contents is always extremely risky...)

To test this theory try creating a new job card with user field data and then change the job number several times. Do *new* records work OK? When doing this test, try to use an unusual job number that won't clash with a real job number that might still be sitting in the system as bad data.

If this is the problem there is just no automatic way for the software to clean this up. It can't tell which records in STAUX000.DBF are the "good" ones and which are the "bad" ones. In fact the end-user may not be able to tell either! It might be safest not to change job numbers for old records, and start with a new job number sequence only for new jobs from now on.

One other thing to do that's important: load SMAUX000.DBF into Excel and sort the spreadsheet on the job number column. Make sure there are no job numbers *already there* for the new job sequence you intend to use. If they are, you'll have problems even when adding new jobs.

You could also load SMJOBS.DBF into Excel and then compare the job numbers between it and SMAUX000.DBF and see what is out of sync for yourself. However, it's up to you if you really want to go that kind of trouble... but it might give you some clues as to the extent of the problem/damage in there.

E.g., you could sort both tables by the job number column, then compare them side by side, looking at the job number sequence and checking for duplicates or missing records. (Missing records may be "normal" but duplicates definitely are NOT.) However, even if you go to that trouble it's not clear to me what you could really do about it, as per my comments above. Selecting and sticking with a fresh transaction sequence should allow the user to continue to use the existing database even if some of the older records in it have some damage.