I have had luck finding the bad record by creating a crystal report with all the fields.  Preview or export to excel and see what record it fails on.  Use DFDM and look at the records at and after the failure to see if you can see an issue.  You can in a test copy delete records you might think is the issue and then retry the verification test then you narrow down for sure if the record is the issue.

I've had data that looked right on the screen but had hidden characters i had to delete to fix it.

Hi, so I was able to dump all the data into a crystal report, and even export the data to excel. There were no errors during this process. Is there something else I should be looking for in the data? Perhaps something I could find within the excel file?

Thanks again!

I went ahead and rebuilt all the files, still getting the same error when I try to run an integrity check on the file.

IM_ItemCustomerHistoryByPeriod.M4T
     The read test failed.  Error #11: Record not found or Duplicate key on write

Don't notice any problems when I run a stock status report. I don't know if this is a problem or not.

Could something be wrong with the integrity check algorithm?

I've always hated error #11.  Note that is says NOT FOUND  or DUPLICATE KEY.  Two very different issues when you are trying to figure out what is going on.

You might try doing a crystal report to see if you can isolate a duplicate key?

Hey Tom - In case you're ever on Mas90 Jeopardy Trivia, "Record Not Found" means your Error 11 was on  READ while "Duplicate Key" means it was on a WRITE. In this case in the Rebuild program itself "The read test failed" is the hard-coded part of the error message ergo it is "Record Not Found" on READ.

MOB - This might be an "internal Error 11" meaning inside the physical file, in the key block of the data, there could be a key with an invalid record pointer. This is different from a normal Error 11. Anyway if this is true for you, it's not something you could spot with DFDM or via a report. Your best option is probably not to do anything at all as it's unlikely regular Sage programming would run into the error on this table.

But if you're so inclined, there is an internal file repair utility (build into ProvideX but not created by Sage) that is not dictionary based that could be tried but you need to run it with an abundance of caution and make a backup of the file first. It's called *UFAR and it's cousin is called *UFAC (C = check, R = Repair). You might see odd results and then get confused more. E.g. Before running say you have 5,000 recs in the file/table. But after running you end up with 5,005, which means the rec count was wrong to begin with b/c the "file information block" was bad to begin with. Or it could say you have 4,995 recs but it won't say which 5 were removed. Maybe those 5 were not legit to begin with. I can't predict what you'll run into. Like everyone has said here, run on a copy / test company. To get more info on UFAR, you can search on it here in SageCity. The old KB article I wrote on it way back when I worked for Sage might still in the current KB but remember the abundance of caution I mentioned. 

Tom thanks for the comment.

Alnoor,

You wrote:

Your best option is probably not to do nothing at all as it's unlikely regular Sage programming would run into the error on this table.

Are you saying not to do anything, in other words ignore the issue? The double negative confused me.

I am currently running the UFAC on a copy of the file from the test company just to see if it finds anything. I appreciate the input thus far.

Alnoor,

I ran the UFAC utility and by the looks of the output it didn't find any errors?

Thanks.

UFAC just doesn't go as far as UFAR does. You won't know until you run UFAR. I said your best option is probably not to do anything b/c unless I was remoted into your system myself and running this for you, I would not be comfortable with assessing the results and providing guidance on the next steps. Unfortunately, running UFAR is a gray area and results are not predictable even after running UFAC.

Have you asked Sage support to move it to second tier and have them try to fix it?  You may have to push for them to do it.  I had a issue one time and they had to take the file to fix it.  We had to be out and not run data when they were working on it.

Bvulliamy,

Thanks for the reply. No, didn't know to do that. They weren't too keen on helping us with this one. We're currently still running version 4.50.8 and it's not supported by them anymore. That's one of the main reasons we are doing the upgrade.

When I did speak to them they seemed to think it was an issue we shouldn't worry too much about. They suggested I write in the forum here and see if anyone else had any ideas.

I have Sage 2018 running along side our 4.50.8 version. It appears to be operating okay, but I was concerned about this one file in particular. Maybe they can look at the file running in the 2018 version.

We have to switch over to 2018 eventually, and the file is failing the integrity check in both 4.50.8 and 2018.

Alnoor,

I ran UFAR on the test company file. I followed instructions found here...

https://support.na.sage.com/selfservice/viewContent.do?externalId=32360&sliceId=1&ispopup=true

The record count before and after running did not change. I'm running the integrity check on the file anyway after running UFAR to see if anything changed.

Integrity check still fails.

Integrity check still fails.

I wrote those exact instructions myself for a KB article when I worked for Sage. However it was for the Sy0ctl.soa file not GL_DetailPosting and not IM_ItemCustomerHistoryByPeriod. I would not have selected D-Wrong and not have selected E-Wrong based on what I see in the dictionary for IM_ItemCustomerHistoryByPeriod. Also I may have rebuilt to Physical End of Record instead of Highest Active Index depending on the physical size of your file in the o/s compared to an estimate of what it should be based on the number of records.

However like you alluded to earlier, it's also possible integrity check is showing a false positive.

Given that, without actually remoting into your system to troubleshoot (or you sending me your data file and dictionary files), I couldn't tell you which scenario is correct. I also wrote my own Rebuild tool to repair certain kinds of damage but I would do an assessment first to identify the underlying issue so the appropriate course of action is taken.

You could try Sage Support again but it's unlikely they will have enough developer knowledge to figure this out. If you choose to take it further I recommend seeking out a ProvideX developer. You could ask your reseller to find one or you could look closer  :-)