Today in the course of my work, I came across a situation where some of the files referred to in our SQL database were not actually on disk where we thought they were.
This was a largeish database of files (over 10,000), and we thought there might be as many as 1600 files missing, so I didn't want to go through each one manually to find the missing files. That led me to this solution: creating a function in SQL to check if the files exist.
The first method I tried for doing this used an undocumented system stored procedure in MSSQL, called xp_fileexist. The code for that looks like this:
1-- using MSSQL built-in stored proc xp_fileexist
2CREATE FUNCTION FileExists(@File varchar(255)) RETURNS BIT AS
3BEGIN
4DECLARE @i int
5EXEC master..xp_fileexist @File, @i out
6RETURN @i
7END
It's a pretty simple wrapper around the stored-procedure. Implementing it as a function provides a more versatile tool for querying however, as shown in this example usage:
1--- usage
2SELECT *
3FROM tbl_FileInformation
4WHERE (dbo.FileExists(PathAndFile) = 'True')
Unfortunately, this didn't do the trick for us at that time. MS SQL server apparently cannot, under any circumstances, see mapped drives. All of our data was on a drive called P:, which was mapped to a network accessible storage device, that our whole company uses. Not to be discouraged, I thought to myself "Well, perhaps it's just a limitation of the xp_cmdshell options, not SQL server as a whole. May there's another way of finding this out...". So that led me to write this next function, which uses Scripting.FileSystemObject via the OLE Automation Options. First things first, I needed to run the following commands to enable OLE Automation, to make it possible:
1-- configuring for use of scripting object
2sp_configure 'show advanced options', 1;
3GO RECONFIGURE;
4GO sp_configure 'Ole Automation Procedures', 1;
5GO RECONFIGURE;
6GO
That's the SQL native way, the other option is to use Surface Area Configuartion and enable it via the check-box. Once that was out of the way, I could try out my function...
1-- Using the scripting object
2CREATE FUNCTION FileExists(@File varchar(255)) RETURNS BIT AS
3BEGIN
4declare @objFSys int
5declare @i int
6
7exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
8exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
9exec sp_OADestroy @objFSys
10
11return @i
12END
But... unfortunately, this gave the same results.
So, the moral of the story? Kids, MS SQL just can't see mapped drives. Give it up now!
That said, UNC paths work fine and if you're lucky enough to have all your data on a drive that's local to the SQL server, and find yourself needing to know if a file you've got referenced still exists, then give these methods a try! YMMV.
** Update **: Well, duh, what I wasn't paying attention to at the time was that the mapped drive wasn't mapped on the SQL server. If this was true, it probably would have worked. Either way -- storing absolute paths in the database, if they aren't UNC paths, is just asking for trouble.