The title kind of says it, but I’ll elaborate a bit.
I use SQL Server 2005, and I'm writing an export query that’ll return a list of users. Some users have profile images, some don’t. The profile image is not represented in the database, at all. There’s just a file on the filesystem, named [username].jpg. So what I need to do is to check if such a file exists for the user.
Well if you must you could always do the following
DECLARE @fso int,
@hr int
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
From that you may (if hr=0) get a good old FileSystemObject.
From that you should be able to do the following
EXECUTE sp_OAMethod @fso, 'FileExists', @hr OUT, @FileName
But really, for wanting to do this, you're going to hell **grin**
Of course this depends on a lot of things, your SQL server configuration, the user it runs under, etc etc. Your mileage may vary.
Is there a good reason not to store the images in the database? You don't really want to access the file system from your database back-end, because giving a database user filesystem access has pretty big security implications. That leaves two options: store the images in the database, or access the files only from your client application.
If you must do this from within sql you could use xp_cmdshell and 'if exist'. This isn't enabled by default for security reasons, so be careful.
enable xp_cmdshell:
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'xp_cmdshell', 1
reconfigure
use xp_cmdshell:
xp_cmdshell 'if exist c:\blargh.txt echo 1'