Tired of getting this?
Here I'll share a simple query to detect if DDJ file exist to help us find missing DDJ files in our Media.
Important Notes:
- Since DDJ is in client-side, you gotta restore your server's SHARD db to your local laptop/pc first and run this from local SQL server, don't do this in the server (unless you have client files uploaded there)
- you have to login SQL using "sa" and not other user you made for your SRO db. (needs access to "master" db and built-in "xp_fileexist" function)
Now the boring part:
Code:
USE [master]
GO
/* Find missing DDJ -- Witchy Moo */
DECLARE @ClientPath VARCHAR(250)
SET @ClientPath = 'E:\vSRO\EasternOnlineSource\Media\icon\' -- WITH TRAILING SLASH
SET NOCOUNT ON
/* Create temporary table */
CREATE TABLE #DDJStatus (FilePath VARCHAR(300),FileStatus VARCHAR(30))
DECLARE DDJCursor CURSOR LOCAL FAST_FORWARD FOR
(SELECT DISTINCT AssocFileIcon128 FROM [SRO_VT_SHARD].[dbo].[_RefObjCommon] WHERE AssocFileIcon128 NOT LIKE 'xxx')
OPEN DDJCursor;
DECLARE @FullPath VARCHAR(250)
DECLARE @isExists INT
FETCH FROM DDJCursor INTO @FullPath
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FullPath = @ClientPath + @FullPath
EXEC xp_fileexist @FullPath, @isExists out
IF (@isExists = 1)
BEGIN
INSERT INTO #DDJStatus values(@FullPath,'OK')
END
ELSE BEGIN
INSERT INTO #DDJStatus values(@FullPath,'MISSING')
END
FETCH FROM DDJCursor INTO @FullPath
END
CLOSE DDJCursor
DEALLOCATE DDJCursor
/* Dump info from temporary table */
SELECT * FROM #DDJStatus
/* Drop temporary table */
DROP TABLE #DDJStatus
GO
You should get something like this:
Save the result to file, and find the missing DDJ
Good luck, hope it helps a little bit