TSQL and filesystemobject - retrieving filenames

  • I have failed to achieve my purpose of a list of filenames from a directory, via the filesystemobject.

    A summary of my strategy follows:

    -- Returns an integer value identifying the 'folder' object, which can then

    -- be used to retrieve its attributes:

    EXEC @rtrnVal = sp_OAGetProperty @fldr, 'Files', @fldrFiles OUT

    PRINT 'GetFolder: Files: ' + CAST(@fldrFiles as varchar(10)) + CHAR(13)

    -- Returns an integer value identifying the 'file' object, which can then be

    -- used to retrieve its attributes -- BASED ON A SPECIFIED PATH/FILENAME:

    EXEC @rtrnVal = sp_OAMethod @fso, 'GetFile', @file OUT, @FullPathFileName

    -- ATTEMPTING TO USE THE INTEGER ID FOR A FOLDER TO RETRIEVE FILE

    -- ATTRIBUTES IS A 'NO-GO':

    EXEC @rtrnVal = sp_OAMethod @fso, 'GetFile', @file OUT, @fldrFiles

    The Folder 'Files' property returns (apparently) an array of files and their attributes (accessible via the integer ID); since there are no arrays in T-SQL (?), I have not been able to retrieve a list of filenames, etc. based on this object.

    Any similar stories? Any insights?

    (I simply reverted to xp_cmdshell 'dir ...' and parsed the results.)

  • I have to admit, I use xp_cmdshell for this one.

    Turn it on, run the command, turn it back off. That keeps it secure.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Long ago, ok like a year ago.. I built a SQLCLR TVF to do this, and it was FAST! Other than that xp_cmdhsell is about the best. It is REALLY hard (impossible?) to correctly instantiate a FSO to do this.

    A long time ago, (seriously this time), like 8 years ago I ran accross this bit of code that I have since lost that allows you to instantiate a VBscript host using the sp_OA commands. You could look for something like that but it is TOUGH to debug.

    CEWII

  • --Turn it on, run the command, turn it back off. That keeps it secure.

    Yup, that's the solution we've used. We also are using xp_cmdshell in part of our automated backup strategy and followed the same security strategy you noted. Thanks.

  • Also considered VBScript and .net CLR options as well. We aren't using much of that within our databases proper; more with our web applications. So the xp_cmdshell may not be the most efficient or elegant, but it does get the job done -- allowing me to move on to other tasks. Thanks for sharing your experience.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply