I have a script that can run in SSMS and compares a path set in TMS with the contents of a folder on a drive. It returns a list the contents of both, and includes statuses as to which media records in TMS are no longer valid, and what files are not entered in TMS.

It works using xp_cmdshell, which has to be enabled in SQL Server. You also need to map the drives in SQL server. Both topics are covered in this article: https://www.mssqltips.com/sqlservertip/3499/make-network-path-visible-for-sql-server-backup-and-restore-in-ssms/

CREATE TABLE #FileSystem ([FileName] NVARCHAR(512), Depth INT, IsFile BIT);
CREATE TABLE #MediaFiles ([FileName] NVARCHAR(512), FileID INT, Rendition NVARCHAR(64));

DECLARE @PATH NVARCHAR(512), @PATHID INT;

/* Change the value here for a single path key */
SET @PATHID = 15;

SELECT @PATH = [Path] FROM MediaPaths WHERE PathID = @PATHID;

INSERT INTO #FileSystem ([FileName], Depth, IsFile)
EXEC xp_dirtree @PATH, 0, 1

INSERT INTO #MediaFiles ([FileName], FileID, Rendition)
(SELECT MF.[FileName], MF.FileID, MR.RenditionNumber
FROM MediaFiles MF
INNER JOIN MediaRenditions MR ON MF.RenditionID = MR.RenditionID
WHERE MF.PathID = @PATHID)

SELECT @PATH AS [Summary]
UNION ALL
SELECT CONCAT('Total Missing from TMS: ',(SELECT COUNT(*) FROM #FileSystem WHERE Depth = 1 AND IsFile = 1 AND [FileName] NOT IN (SELECT [FileName] FROM #MediaFiles)))
UNION ALL
SELECT CONCAT('Total Missing from Filesystem: ',(SELECT COUNT(*) FROM #MediaFiles WHERE [FileName] NOT IN (SELECT [FileName] FROM #FileSystem WHERE Depth = 1 AND IsFile = 1)))

SELECT COALESCE(MF.[FileName],FS.[FileName]) AS [FileName]
    ,CASE WHEN MF.[FileName] IS NULL THEN 'File not referenced in TMS'
   WHEN  FS.[FileName] IS NULL THEN 'File not present on drive'
   ELSE 'OKAY' END AS [Status]
    ,ISNULL(MF.Rendition,'') AS RenditionNumber
FROM #MediaFiles MF
FULL OUTER JOIN #FileSystem FS ON MF.[FileName] = FS.[FileName] AND  Depth = 1 AND IsFile = 1
ORDER BY COALESCE(MF.[FileName],FS.[FileName])

TRUNCATE TABLE #FileSystem;
TRUNCATE TABLE #MediaFiles;

On Tue, Feb 26, 2019 at 11:45 AM Moore, Steven <[log in to unmask]> wrote:
Hey, thanks Judith.

I figured my email was already long enough, so I didn't explain the caveats.  What I did will have issues with two main, but common, things:

1) "forbidden" characters, notable the single apostrophe (O'Keefe) and semi-colon.  - https://stackoverflow.com/questions/1976007/what-characters-are-forbidden-in-windows-and-linux-directory-names

2) and this is huge for you...non-English characters.  Seeing that you are from Zürcher Hochschule der Künste, this is probably a problem.  I think there is a way around it but I haven't checked.
 
But it works for a lot of cases.  BTW, I really like the post by Danielle this AM. 

Steve

Steven MOORE

Developer & Database Administrator

Department of Information Technology


The Museum of Modern Art

11 West 53 Street

New York, NY 10019

813.240.0587




On Tue, Feb 26, 2019 at 11:26 AM Scheiber Dahou Judith <[log in to unmask]> wrote:

Dear Steve

Thank you very much for this nice feature and your perfect instructions! I could detect broken links in no time. But my error list of broken paths became huge. I took some samples and checked in TMS – all were working paths and files. I guess the check stumbles over divided path information. In TMS sometimes a part of the path is entered in the field filename. This looks then like this: doc\strunck;M_presse.pdf.

Best regards

Judith

 

 

—

Zürcher Hochschule der Künste

Zurich University of the Arts

—

Judith Scheiber Dahou

Projektleiterin ITZ Planung

—

Pfingstweidstrasse 96, Postfach, 8031 Zürich

Tel. +41 43 446 22 06

[log in to unmask]

—

www.zhdk.ch / http://www.emuseum.ch/

 

 

 

 

Von: The Museum System (TMS) Users <[log in to unmask]> Im Auftrag von Moore, Steven
Gesendet: Montag, 25. Februar 2019 23:06
An: [log in to unmask]
Betreff: Re: images with broken paths

 

This question comes up every now and then for IT, DAM, and TMS.  The same question occurs for a list of URLs.  So I have a little program that checks both.

 

First, create a list of TMS files with this query (note: just 100 to test):

 

SELECT TOP 100 path = mp.[Path] + mf.[FileName]
FROM [MediaFiles] mf
INNER JOIN [MediaPaths] mp ON mf.PathID = mp.PathID

 

Save the results as a .txt file.

 

Download two files from here (they are safe):

 

 

Save the files in the same folder.  The file called LinkChecker.exe.config has the settings below.  Open in Notepad and edit as needed.

 

  <appSettings>
    <!-- mode choices are "FILE" or "URL" -->
    <add key="mode" value="FILE" />
    <add key="links" value="C:\Temp\files.txt" />
    <add key="errors" value="C:\Temp\errors.txt" />
  </appSettings> 

 

- mode: if you are checking files put FILE.  Use URL for web links.

- links:  this is the list of files to check (ie, results from the SQL above)

- errors: when done, this will have the list of files that do not exist where the database thinks they should be

 

"links" and "errors" can be named whatever you want and can point to wherever you want.

 

Open the path to LinkChecker.exe in a command prompt. and run it like this...

 

C:\MyMuseum\Misc>LinkChecker.exe

 

Check the errors file when it is done.

 

Steve

 

On Mon, Feb 25, 2019 at 3:21 PM Becky Sanchez <[log in to unmask]> wrote:

I am also having this issue. If there is a script or query suggestion, can you please send it to me as well? ([log in to unmask])

Thanks!

-----Original Message-----
From: The Museum System (TMS) Users <[log in to unmask]> On Behalf Of Caroline Im
Sent: Monday, February 25, 2019 12:43 PM
To: [log in to unmask]
Subject: images with broken paths

Hello,

We moved servers recently and in the move, some of the image paths got changed (although not all). This resulted in objects that have thumbnail images that work but broken links to old paths to where the images used to be but no longer exist.

I've tried to locate which objects don't have an image manually but it's taking quite a bit of time. I'm not even sure if I've found them all or not. Is there either an advanced query or a sql script that would help me locate images with broken links (but do have thumbnails)?

I'd be happy for any insight, thank you!

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

     signoff TMSUSERS

     //  eoj


You will receive a confirmation that your subscription has been removed.
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

     signoff TMSUSERS

     //  eoj


You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.