getting filename via filesystemobject

• mcc

  Grasshopper

  Points: 12

  More actions

  March 21, 2002 at 2:48 pm

  #78180

  Hi! I'm trying to get the filenames in a folder as follows:

  Declare @fso int, @fld int, @filename varchar(50), @rtn int, @filecount int

  Exec @rtn = sp_OACreate 'Scripting.FileSystemObject', @fso OUT

  Exec @rtn = sp_OAMethod @fso, 'GetFolder', @fld OUT, 'c:\'

  Exec @rtn = sp_OAGetProperty @fld, 'Files.Count', @filecount OUT

  Exec @rtn = sp_OAGetProperty @fld,'Files(1).Name',@filename OUT

  If @rtn <> 0 Begin Exec sp_displayoaerrorinfo @fld, @rtn End

  Exec @rtn = sp_OADestroy @fso

  Exec @rtn = sp_OADestroy @fld

  select @filecount, @filename,@fld

  I get the File Count, but can't seem to get the name. The error message doesn't specify the problem. Can you help find where I went wrong?

  Thanks for any help! L.

• jamestow

  SSC Veteran

  Points: 211

  More actions

  March 21, 2002 at 3:27 pm

  #425882

  A rather more simple way to do it (if you have rights to do so) is to use xp_cmdshell in the following manner.

  CREATE TABLE TMP_TABLE (FILENAME VARCHAR(20))

  INSERT INTO TMP_TABLE

  EXEC master..xp_cmdshell 'dir /b'

  SELECT * FROM TMP_TABLE

  That will execute the DIR command and return the list of files. You can fully qualify the path.

• mcc

  Grasshopper

  Points: 12

  More actions

  March 21, 2002 at 3:40 pm

  #425884

  Thanks for the response!

  I tried that way first, but the directory I need to look at doesn't reside on the server where the code will run. I couldn't figure out how to make it see the remote directory without mapping a drive, which is something I avoid.

  (that will be my 'Plan B')

• jamestow

  SSC Veteran

  Points: 211

  More actions

  March 21, 2002 at 3:52 pm

  #425885

  Well, aside from that I can't help. Due to security I can't execute those SP's.

  Edited by - jamestow on 03/21/2002 3:53:17 PM

• SamiU

  SSCrazy

  Points: 2277

  More actions

  March 22, 2002 at 12:42 pm

  #425945

  Can you use a UNC path? I read the directory from a remote server with the following code:

  create table #temp (filenam varchar(30))

  insert into #temp

  exec master..xp_cmdshell 'dir /b \\SERVERNAME\SHARENAME\*.*'

  select * from #temp

  drop table #temp

  Using a server named TEST and a share of the C drive (named C$), the \\SERVERNAME\SHARENAME combination becomes \\TEST\C$. The entire line of code becomes exec master..xp_cmdshell 'dir /b \\TEST\C$\*.*'

  You don't need to map a drive in this case. I am not sure if this solves your problem, but, I hope this is of some help. Best of Luck to you.