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:
-- using MSSQL built-in stored proc xp_fileexist
CREATE FUNCTION FileExists(@File varchar(255)) RETURNS BIT AS
BEGIN
DECLARE @i int
EXEC master..xp_fileexist @File, @i out
RETURN @i
END
It's a pretty simple wrapper around the stored-procedure. Implimenting it as a function provides a more versatile tool for querying howver, as shown in this example usage:
--- usage
SELECT *
FROM tbl_FileInformation
WHERE (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:
-- configuring for use of scripting object
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
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...
-- Using the scripting object
CREATE FUNCTION FileExists(@File varchar(255)) RETURNS BIT AS
BEGIN
declare @objFSys int
declare @i int
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
exec sp_OADestroy @objFSys
return @i
END
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!
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.
4 comments:
Its not all bad, xp_fileexist will work with UNC paths. However the shared folders will have to have fairly open security settings as I'm not sure what Security Context its run in.
UNC path works
Yes! UNC does work.. If you have the luxury of using it, and know the real destiantions of the mappings and the computer names and what not... Anyhow, my point though is that referencing mapped drives will not work in any circumstances, which is rather strange.
Mapped drives won't work as they exist only in the context of the logged in user. Sql will be running as a different context (Probably as network service.)
If you changed your routine to map the drives before trying to access the files it would work, but only if the SQL server account has sufficient privileges to access the appropriate unc paths. It's easier to just use the unc paths.
Post a Comment