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.
