01-30-2014 09:37 AM
I was running my semiweekly document store audit and Trim indicated one file was missing, which isn't necessarily odd, I usually find it was probably caused by a malfunction of the Trim client somewhere in our organization. Anyway, the audit log gives a URI number and a document store reference number:
Revisions Revision Number 1 (uri=102082) : store reference (006+00G+0E0H01GO070.PDF) does not exist.
To track down the record affected by this type of problem, I can usually perform a search of the latter in the workgroup server logging to find the number of the offending record. To do this I go to the document store and look at the date created timestamps of the electronic documents that come before and after the missing document (since the documents are named incrementally) so I know which day's logs to search in. However in this instance the filename in question does not appear in either the WG or audit logging.
Before resorting to having to look for SQL queries to perform on the database to identify the offending record, I figured I'd try some string searches in the client. A URI search for the record (uri:102082) pulls up a completely unrelated record that was finalized years ago. I saw in the appendix that there is a "store" string search method, but I couldn't get any results by using searches like "store:0E0H01GO070", "store:0E0H01GO070.PDF", or "store:006+00G+0E0H01GO070.PDF".
What string search, if any, can be used to identify the record with the missing document? If the string search isn't capable of that, what would be an appropriate SQL query to use, and on which table? Thank you in advance for your help.
Solved! Go to Solution.
01-30-2014 12:35 PM - edited 01-30-2014 12:36 PM
You can't use a string search. You can use this SQL query though:
select recordId from tsrecord where uri in (select evRecElecUri from tserecvsn where uri = 102082)
If you didn't know the URI (which you do because its' in the log) but you knew the file name in the store, you could use this:
select recordId from tsrecord where uri in (select evRecElecUri from tserecvsn where evSID='006+00G+0E0H01GO070.PDF')
EDIT: note this only works for revisions. Use the same logic on the TSRECELEC table if it's not a revision.
01-30-2014 02:01 PM
Thank you, it sounds like revisions have their own URI numbering that is separate from that of regular records and I didn't realize that was the case. The query pointed out the correct record, thank you.
Do you think there's any chance HP would be willing to add a string search method to search for a revision URI instead of just a record URI? It could be helpful in cases like this. Also, do you know how the store search method would be used?
02-13-2014 01:36 PM
Thanks, I figured it would be that, but the SQL query gives no results?
That would be the right query to use based on this docstore audit log entry, right:
Renditions 055-00048 Warning Letter Response 062509.pdf (uri=38416) : store reference (002+00S+0D9U1C3T0IE.PDF) does not exist.
02-25-2014 10:05 AM
I ran into a similar problem. This morning's document store audit shows that a revision of a record has an electronic document with an "incorrect file size." I don't know how this is possible except for a malfunction of the server/client, it's not like anybody directly manipulates the document store. Re-running the docstore integrity check and selecting the option to correct any errors that are encountered does not correct this problem
Can the client do this any more cleanly than me re-adding the file to replace that revision so that Trim records the correct file size? Is there a better way to resolve this issue?
02-25-2014 10:11 AM
The client do anything other than what you described. The DBA could go update it manually in the databaes though (not worth the effort in my opinion).
02-25-2014 10:20 AM
I guess I was expecting there to be an obvious way to replace the revision via the thick client, but after I extracted that revision, I'm not seeing a way to overwrite the electronic document for that revision. What am I missing here?
02-25-2014 10:30 AM
The integrity check fix feature should solve the problem. Here is the documentation from that feature:
The integrity check can fix reported incorrect file size.
The method depends on whether or not there is a backup path specified in the field Restored Backup Folder For Store:
If there is no backup folder specified, by updating the dataset's last known file size to match the size of the item as it actually is in the store
If there is a backup folder specified, HP TRIM looks in the specified backup folder for the document.
If HP TRIM finds it and the document is the expected size, HP TRIM restores it to the document store and changes the references to point to the restored item.
If HP TRIM does not find the document, it will take no action.
02-25-2014 10:53 AM
It wouldn't allow me to begin the integrity check with the option to repair errors unless I specified a backup folder, so I made sure that the file from the document store was at the root of the backup folder path. This check takes a long time to finish so I won't know for a while if it worked. It doesn't need me to build the entire folder structure to the document in the backup folder, does it?
02-25-2014 11:27 AM
It didn't work, or at least the integrity check's logging says it didn't correct the issue. Unless it has a requirement like having some kind of folder structure in the backup folder, I can't tell that the integrity check will be able to fix it. What would be the appropriate SQL query to update the file size?
02-27-2014 09:03 AM
Today is the day I regularly run the second document store audit of the week. Since I need to get this fixed, I guess I'll have to open a support ticket.
02-27-2014 09:17 AM
The backup location you use must maintani the full structure. So if you're bad file is in "TP\2\001\000" off the original document store, copy that full path structure and the file into a new place. Then point the backup field to that new place.
02-27-2014 09:33 AM
After looking at the documentation (help file) again, that option will not resolve the problem anyway. The help file says that if the backup folder is specified AND the document in question is found there with the size Trim is expecting, it will put it in the document store so that the file with the expected size is present. There is no file with the expected size that I can provide since I suspect that it was a malfunction of the software that caused the wrong file size to be recorded into the database. Since this is an old revision of the file, I don't want to make a mess of things by deleting the revision and adding a new revision, since as you said, Trim has no way for me to replace the document on a past revision.
This leaves editing the size in the database to be the last option I can think of, but I need the relevant SQL queries to do so.
02-27-2014 10:14 AM
update tserecvsn set evBytes = 1234 where evRecElecUri = (select uri from tsrecord where recordId = '<yourRecordNumberHere>') AND evVersionNbr = <yourRevisionNumberHere>
02-27-2014 12:05 PM
Thank you, that worked. The solution suggested by HP support was to delete the offending revision and optionally add it in again as a new revision, but that would put it out of order with the other past revisions and I wanted to keep the record as intact as possible. Altering the file size in the database seemed to be the only easy way to correct the problem without deleting the revision, would you consider that an accurate conclusion?
02-27-2014 12:09 PM
Thanks again. I'm going to ask HP support to correct the documentation to match the Trim client's behavior regarding a specified backup folder being mandatory when running the integrity check with the option to correct errors.
04-15-2014 10:49 AM
It looks like the inability to start the integrity check with the option to correct errors unless a restored backup folder is specified was a bug with Trim 7.1.x, 7.3.4 allows this to be done.