Search code examples
sql-servert-sql

How do I get a filelist from the local filesystem in SQL Server 2005?

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.

Solution

  • 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.