Moved to: WoodyHayday.com
Woody Hayday

Fresh Ideas

Hi there! This is my old blog, I don't hang around here much.
You can now find me trying to mix things up here:
Visit WoodyHayday.com Subscribe To My Email List

xp_fileexist Network Drive – T SQL Based File checking

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.

This entry was posted in Social Media, SQL Server, Transactional SQL. Bookmark the permalink. Both comments and trackbacks are currently closed.
Woody Hayday

Comments Archive

Hi there. This is my old blog and it's archived, so you can no longer post new comments on this post (xp_fileexist Network Drive – T SQL Based File checking).

Read my new blog about writing software and stories at WoodyHayday.com

The New Blog
WoodyHayday.com
A Quote..
"Advance, and never halt, for advancing is perfection. Advance and do not fear the thorns in the path, for they draw only corrupt blood."
Khalil Gibran
Old Random Projects
    © Woody Hayday 2008-2017