none
list all folders, and subfolders, in a directory

    Question

  • I'm trying to find a way to list all folders, and subfolders, in a directory.  I ran the code below and got a message that says 'Access is Denied'.  There is no way access is denied to that folder. How can I list all folders, and subfolders, in a directory?

    --'C:\Users\Ryan\Desktop\Coding\';
    set nocount on

    declare @curdir nvarchar(400)
    declare @line varchar(400)
    declare @command varchar(400)
    declare @counter int

    If (select count (*) from sys.objects where name='Output') <> 0 DROP TABLE output
    If (select count (*) from tempdb.sys.objects where name like '%#Tempoutput%') <> 0 DROP TABLE #Tempoutput  
    If (select count (*) from tempdb.sys.objects where name like '%#dirs%') <> 0 DROP TABLE #dirs  

    create table #dirs (DIRID int identity(1,1), directory varchar(400))
      Set @command = 'dir "C:\Users\Ryan\Desktop\Coding\Microsoft Access\" /S/O/B/A:D'
     insert into #dirs exec xp_cmdshell @command
      set @counter = (select count(*) from #dirs)
    create table #tempoutput (line varchar(400))
    create table output (Directory varchar(400), FileSize varchar(400))
        While @Counter <> 0
          Begin
            Declare @filesize int
            set @curdir = (select directory from #dirs where DIRID = @counter)
            set @command = 'dir "' + @curdir +'"'
            insert into #tempoutput
            exec master.dbo.xp_cmdshell @command
               select @line = ltrim(replace(substring(line, charindex(')', line)+1,len(line)), ',', ''))
               from #tempoutput where line like '%File(s)%bytes'
               Set @filesize  = Replace(@line, ' bytes', '')
            Insert into output (directory, Filesize) values (@curdir, @filesize)
            Set @counter = @counter -1
           End
           Delete from output where Directory is null
    select * from output


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, January 25, 2014 5:25 PM

Answers

  • The service account for SQL Server needs to have access to that path,


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 25, 2014 5:43 PM
  • Right-click the folder, select Properties and then the Securities tab.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by ryguy72 Monday, February 17, 2014 4:31 PM
    Tuesday, February 11, 2014 8:38 AM
  • And you set the permissions for the serive account? Keep in mind that if you are trying to access a network share, and SQL Server is running under LocalSystem or somesuch, it may not work that well.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by ryguy72 Monday, February 17, 2014 4:31 PM
    Wednesday, February 12, 2014 8:42 AM

All replies

  • The service account for SQL Server needs to have access to that path,


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 25, 2014 5:43 PM
  • Ok, how do I set that up???

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, February 11, 2014 2:35 AM
  • Right-click the folder, select Properties and then the Securities tab.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by ryguy72 Monday, February 17, 2014 4:31 PM
    Tuesday, February 11, 2014 8:38 AM
  • I just did what you suggested.  The result of running the script is still the same.

    Everything is set to Full Control; this must be the highest level of permissions/rights.  What am I doing wrong here?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, February 12, 2014 5:27 AM
  • Can you tell us how you're executing this code? Is it called from sql agent job or are you executing it by calling code directly?

    Did you try creating a proxy for xp_cmdshell and then executing it?

    http://www.mssqltips.com/sqlservertip/2143/creating-a-sql-server-proxy-account-to-run-xpcmdshell/


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, February 12, 2014 5:34 AM
  • And you set the permissions for the serive account? Keep in mind that if you are trying to access a network share, and SQL Server is running under LocalSystem or somesuch, it may not work that well.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by ryguy72 Monday, February 17, 2014 4:31 PM
    Wednesday, February 12, 2014 8:42 AM
  • Got it working!Thanks everyone!

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, February 17, 2014 4:32 PM