SQL table that identifies file name and location in archives

Walter,

You'll need to add something like this:

select 'C:\EPDM Vaults\' + dbo.fnHexNumber(documents.documentid,1,1) + '\' + dbo.fnHexNumber(documents.documentid,8,1)

where 'C:\EPDM Vaults\' is the path to your archives as that's not stored in SQL. If you want to link to a specific version of the file under that folder you'll need to hook into the revisions table as well and convert that to hex and append the file extension.

Here is something I wrote that gives you the Archive server (replicated), then vault path, and the archive server path. The "d$" in the archive server path is the "D" drive on the server shared. This is so I can copy paste it into Window sExplorer to browse there from my PC.

Declare @Filename nvarchar(235)

Declare @Vaultname nvarchar(235)

Declare @ArcServerPath nvarchar(235)

SET @Filename='172aj-01.slddrw'

SET @Vaultname='C:\Product Data'

SET @ArcServerPath='d$\SolidWorks Enterprise PDM\Data\Product Data\'

SELECT ARS.ArchiveServerName as Server

      ,ARC.ArchiveID As DocID

      ,DOC.Filename

      ,ARC.VersionUID

      ,REV.RevNr as Ver

      ,@Vaultname + Prj.Path + Doc.Filename as Path

      ,'\\' + ARS.ArchiveServerName + @ArcServerPath + RIGHT(master.dbo.fn_varbintohexstr(ARC.ArchiveID),1) + '\' + right(master.dbo.fn_varbintohexstr(ARC.ArchiveID),8)

       as 'Archive Server Path'

  FROM ArchiveServerStored ARC

  inner join ArchiveServers ARS on ARS.ArchiveServerID=ARC.ArchiveServerID

  inner join Revisions REV on REV.VersionUID=ARC.VersionUID

  inner join Documents DOC on DOC.DocumentID=ARC.ArchiveID

  inner join DocumentsInProjects DIP on DIP.DocumentID=DOC.DocumentID

  inner join Projects PRJ on PRJ.ProjectID=DIP.ProjectID

  where Doc.filename like @Filename

  order by DOC.Filename,Ver