Get File Names from Directory?

• JN-161845

  SSCrazy

  Points: 2052

  More actions

  March 21, 2005 at 2:31 pm

  #60243

  I put this post on T-SQL, too, but wasn't sure if it belongs here or in T-SQL... ?

  Hello,

  I have a photo table in SQL Server with photo name for the main photo and photo's ID. All photos are stored on some drive on disk on some server. Now subsequent photo names for photos are found only in the directory (not stored in any SQL Server table like main photo names are). I have a table that needs to be populated with a PhotoID, PhotNames (however many photos there are) for that PhotoID and photo path. PhotoID and path I can get from a SQL Server table, but only can get the main photo name from a SQL Server table and the rest will have to be looked up in the directory where photos reside based on photo's ID. What would be the best way to do this in DTS? How can I get main photo name from SQL Server table and subsequent photo names from a directory based on a photo ID?

  If anyone has any suggestions, please help.

  Thanks.

  JN

• philcart

  SSC-Forever

  Points: 47794

  More actions

  March 21, 2005 at 4:13 pm

  #548097

  Try using the undocumented extended stored procedure xp_dirtree.

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=128000

   

  --------------------
  Colt 45 - the original point and click interface

• Calvin Lawson

  SSChampion

  Points: 11030

  More actions

  March 22, 2005 at 12:38 pm

  #548303

  Or a homegrown version....

  ALTER           Proc cmd_GetDIR @Dir varchar(255)

  as

   

  set nocount on

  declare @cmd varchar(275),

    @FileName varchar(255)

  set @cmd = 'dir "' + @dir + '" /A-D /B'

   

  exec master.dbo.xp_cmdshell @cmd

  set nocount off

   

   

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  SET ANSI_NULLS ON

  GO

  Signature is NULL