My client uses Document Exchange as a secure document repository for a number of independent (isolated) users.

Lately, one user reported using the DMX search feature and came up with a file from another user that we could not find anywhere in the file tree.

We traced it down to a file that was "orphaned" in the DMX_Entries table. It had a CollectionId of 1359, but there is no row with an EntryId of 1359 in the table. The file had View permission for "All User", so that's why it could be seen. But with no valid CollectionId, it could not be shown (or found) in the tree.

Is there a known problem orphaning files and a way to find and eliminate them?

Is this a valid query to find all orphaned files and folders?

SELECT *
FROM DMX_Entries AS A
WHERE NOT EXISTS (SELECT 1 FROM DMX_Entries AS B
WHERE A.CollectionId = B.EntryId
AND A.PortalId = B.PortalId
AND B.EntryType='Collection')

When we do this query on our database, we come up with 5 orphaned files and 203 folders.

Thanks for your help and guidance with this.

It's surprising to see orphaned files. I'd love to know what could provoke that. But in the meantime: you're on the right track. You've found the SQL to find those records. What you could do is to either delete them (if you feel comfortable with that) or move them to another collection if you know the ID (0 is the root). That way they should show up again. Note that DMX needs to do some housekeeping (e.g. set folder sizes correctly) when you move files (i.e. change the CollectionId value). It does this through a trigger in SQL.

Peter

Thanks for your guidance. I'm not sure what caused the orphaned files. It could be from a delete gone bad (since all the files appear to be old test files from our original days of setting up the repository).

Or it could be related to the custom "auto-prune" module we're using on this site. But I don't think that is it because we probably would have been seeing more orphaned files in that case.

In any case, we will delete the orphans via SQL and move on. Thanks again for your help.