1

I need to get the physical file path of a given document.

Running some querys in the database i figure it out that i can get most of the information in some tables:

The o_path from dms_stor, o_projectcode from dms_proj and the o_filename from [dms_doc].

       SELECT o_node 'Server'
              ,c.o_path + '/' + b.o_projectcode + '/' + o_filename 'Path'
       FROM [PW_Engevix_ECO].[dbo].[dms_doc] a
       JOIN [PW_Engevix_ECO].[dbo].[dms_proj] b ON a.o_projectno = b.o_projectno
       JOIN [PW_Engevix_ECO].[dbo].[dms_stor] c ON c.o_storno = a.o_storno

And the result is similar to:

Server      Path
VIRM-RGR016 D:/PW_RGR_STORAGE/ECX/1197_00/dms00166/

This works for 90% of the cases.

The problem is that some times one more sub-folder is created, it always start with "ver".

End result:

New file at:       
Server  Path
VIRM-RGR016 D:/PW_RGR_STORAGE/ECX/1197_00/dms00166/ 

Old file at:
Server  Path
VIRM-RGR016 D:/PW_RGR_STORAGE/ECX/1197_00/dms00166/ver00000

Sometimes it's created like: ver00001, ver00002 ...

I don't know where or how can i know if the file for a given document is in the root or a sub-folder. I need to be able to know for sure, in what folder the document file is located.

I could try on folder then the other, but this way i won't know for sure if this file belong the that document.

I know that the PW API (aaApi_CopyOutDocument) does that, but i don't know how.

I cannot use the PW API in this current application.

Thanks

Thiago Souza
  • 135
  • 2
  • 8

1 Answers1

1

Try change your carry for this:

SELECT o_node 'Server'
      ,c.o_path + '/' + b.o_projectcode + '/' + CASE 
            WHEN o_original <> 0
                  THEN 'ver' + REPLICATE('0', 5 - LEN(CAST(a.o_version_seq AS NVARCHAR(MAX)))) + CAST(a.o_version_seq AS NVARCHAR(MAX)) + '/' 
            ELSE ''
            END + o_filename 'Path'
FROM [PW_Engevix_ECO].[dbo].[dms_doc] a
JOIN [PW_Engevix_ECO].[dbo].[dms_proj] b ON a.o_projectno = b.o_projectno
JOIN [PW_Engevix_ECO].[dbo].[dms_stor] c ON c.o_storno = a.o_storno

I think this will work...

dionesf
  • 177
  • 8