How do I get a list of files from the local file system in SQL Server 2005?
The name of the type says it, but I understand a little.
I am using SQL Server 2005 and I am writing an export query that returns a list of users. Some users have profile pictures, some don't. The profile picture is not present at all in the database. Theres just a file on the filesystem called [username] .jpg. So I need to check if such a file exists for the user.
source to share
It is good if you can always do the following
DECLARE @fso int,
@hr int
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
From this you can (if hr = 0) get the good old FileSystemObject .
From this you should be able to do the following
EXECUTE sp_OAMethod @fso, 'FileExists', @hr OUT, @FileName
But in fact, in order to do this, you go to hell **** grin ****
Of course it depends on a lot of things, the configuration of your SQL server, the user it is running, etc. etc. Your mileage may vary.
source to share
If you must do this from within sql, you can use xp_cmdshell and "if exist". For security reasons, this is disabled by default, 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'
source to share
Is there a good reason not to store images in the database? You really don't want to access the filesystem from your database, because granting access to the filesystem of the database user has serious security implications. This leaves two options: save the images to the database, or only access the files from your client application.