Woody Hayday

If you didn’t already know you can check whether files exist, find file sizes and all sorts of other useful things directly from within Transactional SQL Query. In this example I had a table containing a few columns that gave me the network drive location and filename of a few thousand files and I wanted to verify that every file at this address existed in reality.

Here is how I managed it:

–make procedure to cycle trhough entrants and assign them to a room
create procedure [dbo].[files_2_check] as
DECLARE @fileAt varchar(2000)
DECLARE @fileEx int

DECLARE checkCursor CURSOR FOR
–select top 10 FileDirectory + FileName from files – USE to test it first
select FileDirectory + FileName fromfiles

OPEN checkCursor

FETCH NEXT FROM checkCursor
INTO @fileAt

WHILE @@FETCH_STATUS = 0
BEGIN

set @fileAt = ‘\\192.168.1.8\f$\Files\’ + @fileAt
–this will depend on your network address

print ‘Searching for ‘+@fileAt
EXEC master..xp_fileexist @fileAt, @fileEx OUTPUT;

if @fileEx = 0
begin
insert into dbo.tbl_fileCheck (fileLoc) Values (@fileAt)
–doesnt check for already existing, archives by date though
print ‘…NOT FOUND – Recorded to DB’
end
if @fileEx = 1
begin
print ‘…OK’
end

set @fileEx = 0
set @fileAt = ”
FETCH NEXT FROM checkCursor
INTO @fileAt
END

CLOSE checkCursor
DEALLOCATE checkCursor

Which basically grabs the rows containing the file directory/filename and uses a cursor to cycle through each one checking them using the xp_fileexist command. There were a number of ways available to do the actual file exist checking but this one suited best, This solution definately works on SQL2005 and forwards, although did need a few additionals:

Turn on the setting (potential security issues if this is on 24/7, but you could run it on a vanila ms sql db server elsewhere) – Worth turning off afterwards.

exec sp_configure ‘show advanced options’, 1

go

reconfigure

go

exec sp_configure ‘xp_cmdshell’, 1

go

reconfigure

go

Apply admin rights to the MS SQL Windows service – by default this will only have local scope, it needs you to go to the services properties, then logon tab and assign it a user which has scope to view the files its checking exist over the network.