ThinkGeek! You"ll love this stuff!

Thursday, September 4, 2008

Is the file there?

I was recently asked to help verify that files actually existed in the locations specified in a field of a table. That is that the database has a table that contains a field that contains a file path and a file. My task (since I chose to accept it) was to validate that the database value actually pointed to a file in the OS.

A rather undocumented extended stored procedure continues to exist in SQL Server 2005 (xp_fileexist). I decided to use it since this task was not for application functionality but simply an ad-hoc query to help troubleshoot an issue. Microsoft discourages the use of undocumented syntax because it does not guarantee support for or existence of that syntax in the future. But for this situation, it was ok.

So basically the thing is used like this:

EXECUTE xp_fileexist 'c:\autoexec.bat', @result OUTPUT

I do suggest you use a OUTPUT variable in this case, since you are checking more than a single file. XP_fileexist can also be used without the OUTPUT variable which will return a result set like this:

File Exists | File is a Directory | Parent Directory Exists
----------- | ------------------- | ------------------
1 | 0 | 1

I however had a list of paths I needed to loop through so first I gathered the relevant data into a temp table. I created variables to hold a single file path value and also an INT OUTPUT variable to return the result (1= exists/ 0 = not exists).

Within the loop, I then update the temp table for each file path so I could report back which file paths were there vs not. Last is just a SELECT from the temp table I just updated (the report).

use PoeticSQL_BlogEntries

--drop table #temp1

SELECT uri, 0 as exists1
INTO #temp1
FROM Cache with (nolock)
WHERE exportmode='Print'
and createdate > '09/04/2008'
and uri like '%223454%'

DECLARE @URI varchar(2000) --to hold currently searched file path
DECLARE @iFileExists int --to hold the return value from the XP

--initialize the varibale
SELECT @URI = '' + min(cachefileuri) + '' FROM #temp1

--THE Loop

EXECUTE master..xp_fileexist @URI , @iFileExists OUTPUT

UPDATE #temp1
SET exists1 = @iFileExists
WHERE cachefileuri = @URI

--get next record
SELECT @URI = min(cachefileuri) FROM #temp1 WHERE cachefileURI > @URI

SELECT * FROM #temp1

--clean up

There are newer solutions to this problem. Some use the OACreate and OADestroy objects which require special permissions and another interesting one I have seen but not tested myself is master..sp_MSget_file_existence.

Good Luck and Happy TSQLing