AnsweredAssumed Answered

SQL table that identifies file name and location in archives

Question asked by Walter Bednarz on Feb 4, 2014
Latest reply on Feb 5, 2014 by Walter Bednarz

Like • Show 0 Likes0
Comment • 11

Is there a way to identify where a file is and what it is named in the archives via sql tables? I am trying to create a SQL query that list documents with their vault path, archive name, and archive location. This is what I have so far.

SELECT Documents.Filename, Projects.Path

FROM Documents JOIN

DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID JOIN

Projects ON Projects.ProjectID = DocumentsInProjects.ProjectID

WHERE Documents.Deleted = 0

ORDER BY Documents.Filename

Charley Saint Feb 5, 2014 12:01 PM

Correct Answer

Walter,

Sorry, I tested that with a limited fileset, you need to add a right to the first fnHexNumber:

SELECT 'C:\Program Files\SolidWorks Enterprise PDM\Data\EPDM-Vault\'+right(dbo.fnHexNumber(Documents.DocumentID,1,1),1)+'\'+dbo.fnHexNumber(Documents.DocumentID,8,1)

See the reply in context

No one else had this question

Outcomes

11 Replies

Charley Saint Feb 4, 2014 11:46 AM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
- Walter Bednarz @ Charley Saint on Feb 5, 2014 11:50 AM
  Mark Correct
  Correct Answer
  Here is what my script looks like now:
  
  SELECT 'C:\Program Files\SolidWorks Enterprise PDM\Data\EPDM-Vault\'+dbo.fnHexNumber(Documents.DocumentID,1,1)+'\'+dbo.fnHexNumber(Documents.DocumentID,8,1), Documents.Filename, Projects.Path
  FROM Documents JOIN
  DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID JOIN
  Projects ON Projects.ProjectID = DocumentsInProjects.ProjectID
  WHERE Documents.Deleted = 0
  ORDER BY Documents.Filename
  
  The results are not what I am hoping for. What am I doing wrong here?
  
  Thank you for the help. Sorry my SQL knowledge is limited.
  Like • Show 0 Likes0
  Actions
  - Charley Saint @ Walter Bednarz on Feb 5, 2014 12:01 PM
    Unmark Correct
    Correct Answer
    Walter,
    
    Sorry, I tested that with a limited fileset, you need to add a right to the first fnHexNumber:
    
    SELECT 'C:\Program Files\SolidWorks Enterprise PDM\Data\EPDM-Vault\'+right(dbo.fnHexNumber(Documents.DocumentID,1,1),1)+'\'+dbo.fnHexNumber(Documents.DocumentID,8,1)
    Like • Show 0 Likes0
    Actions
    - Walter Bednarz @ Charley Saint on Feb 5, 2014 12:17 PM
      Mark Correct
      Correct Answer
      Looks close, but not quite there yet.
      
      Here is the updated SQL query and result (you may have to RMB and open in new window):
      However, the paths don't exist. I tried to find the first file.
      
      Like • Show 0 Likes0
      Actions
      - Charley Saint @ Walter Bednarz on Feb 5, 2014 1:39 PM
        Mark Correct
        Correct Answer
        Walter,
        
        Looks like you're on a replicated server that doesn't have many of the files, you may want to look at Jason's query or setup a replication schedule to get those files copied over. Odd that there's only 2 files in your F folder that are in the 12,000's though, are you sure that's not an old archive location or something?
        Like • Show 0 Likes0
        Actions
        Walter Bednarz @ Charley Saint on Feb 5, 2014 1:44 PM
        Mark Correct
        Correct Answer
        Not a replicated environment. The reason for the small number of files is that it is a test environment. I probably should have mentioned that.
        Like • Show 0 Likes0
        Actions
        Charley Saint @ Walter Bednarz on Feb 5, 2014 4:45 PM
        Mark Correct
        Correct Answer
        Walter,
        
        Not sure, everything looks right. Is that C:\Program Files... path the same as the one listed in the archive servers registry under
        HKEY_LOCAL_MACHINE\SOFTWARE\SolidWorks\Applications\PDMWorks Enterprise\ArchiveServer\Vaults\EPDM-Vault\ArchiveTable
        Like • Show 0 Likes0
        Actions
        Walter Bednarz @ Charley Saint on Feb 5, 2014 5:16 PM
        Mark Correct
        Correct Answer
        You had it right. I had some damage to the archives location. That is why some files were not there. As I looked around more I found files that were showing up correctly. Thank you for the help.
        Like • Show 0 Likes0
        Actions
Jason Capriotti Feb 5, 2014 10:51 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
- Charley Saint @ Jason Capriotti on Feb 5, 2014 11:36 AM
  Mark Correct
  Correct Answer
  Jason,
  
  Note of caution about that script, it only works in replicated environments. If there's only one archive server then the ArchiveServerStored table should always be empty.
  Like • Show 0 Likes0
  Actions
  - Jason Capriotti @ Charley Saint on Feb 5, 2014 12:37 PM
    Mark Correct
    Correct Answer
    Doesn't everyone have replicated environments?
    Like • Show 0 Likes0
    Actions

SQL table that identifies file name and location in archives

Outcomes

Related Content

Recommended Content