November 9, 2009 at 1:03 pm
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.)
November 9, 2009 at 1:58 pm
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
November 9, 2009 at 3:16 pm
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
November 10, 2009 at 7:12 am
--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.
November 10, 2009 at 7:14 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy