Find missing DDJ file using SQL query

[Witchy Moo]

Tired of getting this?


I made simple query to detect if DDJ file exist to help us find missing DDJ files in our Media. (Tested in SQL2K8-R2, not sure if it works in other SQL version)

First,
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)

Second,
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)

Here we go:

Spoiler:

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:

Spoiler:

Save the result to file, and find the missing DDJ ;)

Good luck, hope it helps a little bit ;)

[MentaL]

[Blacksheep25]

Nice! This should be very useful, Keep up the good work Witchy

[lewy1000]

Not working for 2005 mssql :<

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@ClientPath".

[Witchy Moo]

Not working for 2005 mssql :<

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable  @clientPath".

Yeah, in SQL 2005 you cannot assign value while declaring the variable, you must separate variable declaration and value assignment:

DECLARE @clientPath VARCHAR (250)
SET @clientPath = 'your\path'

good luck