Reading the Contents of an FTP folder

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  July 4, 2006 at 9:14 am

  #169455

  Does anyone know any SQL code that can read the contents of a specified folder and insert a list of all the filenames into a table ?

  Any help would be appreciated

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)
  

• mike roberts-342685

  Valued Member

  Points: 71

  More actions

  July 5, 2006 at 7:00 am

  #647486

  Hello mate,

  This will do the trick - simply inserting the DIR result set in to a table and then reading that table.

  Its a temp table, so please try not to run this kind of thing adhocly on a live production server!

  declare

  @scmdstring varchar(1000),

  @sFileLocation varchar(255),

  @sFileSearch varchar(20)

  SET

  @sFileLocation = '\\servername\c$'

  SET

  @sFileSearch = '*.*'

  SELECT

  @sCmdString = 'dir '+ @sFileLocation + @sFileSearch + ' /B'

  PRINT

  @sCmdString

  CREATE

  TABLE #Directory

  (

  Filename varchar(255) NULL)

  INSERT INTO #Directory EXEC master.dbo.xp_cmdshell @sCmdString

  You will also have to enable XP_CMDSHELL in SQL 2005 as it is turned off option.

  Let me know how you get on.

  cheers

  Mike