Yuri Prado Super Contributor.
Super Contributor.
587 views

SQL statement to identify location of document in document store

Hi All, 

I have encoutered a situation where a user accidentally deleted a container with about 50 documents. The issue is that the file is critical to my organisation. As a result I have obtained a copy of the database back up that contains the list of files and also the copy of the document store before the files were deleted. 

I have the record numbers of each of the documents that were deleted so I was wondering if anyone could provide the precise sql statement that would identify the location of the document in the document store (at a windows file level). Out TRIM database is running in SQL. 

Any help would be much appreciated. 

Cheers. 

0 Likes
2 Replies
Jan iCognition Trusted Contributor.
Trusted Contributor.

Re: SQL statement to identify location of document in document store

You will need to find where your document store is located via Enterprise Studio, but here is an example script to determine where the electronic file is located within the store.

SELECT reSID FROM [dbo].[TSRECELEC]
LEFT JOIN [dbo].[TSRECORD] ON [dbo].[TSRECELEC].uri = [dbo].[TSRECORD].uri
WHERE [dbo].[TSRECORD].recordId = '{insert_record_number_here}'

 

This will result in something like:

reSID
001+000X+0H271PEH005.JPG      

If you replace the pluses with slashes you will have your file path - just append the dataset ID and document store file share location.

For example - the record above exists on my network at \\sharedrive\Document Store\RD\1\001\000X

Where RD is my TRIM dataset ID.

Yuri Prado Super Contributor.
Super Contributor.

Re: SQL statement to identify location of document in document store

Superb Jan. Thank you. 

I will give this a go. Confident that we might be able to access the documents without having ot register the database and document store. 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.