0

When running xp_dirtree, it does not seem to read some directories.

Any suggestions? I am wondering if it is some sort of permission kind of thing? While my question has been flagged as a possible duplicate of another question, that question is indecipherable to me - I have no idea what it is actually telling me to do to fix the problem.

I am running Windows 10 and using Microsoft SQL Server 2014 

USE MyDatabase;

DECLARE @files TABLE (FileName varchar(max), depth int, IsFile int)


--INSERT INTO @files  EXEC xp_dirtree 'C:\', 1,1      -- works
--INSERT INTO @files  EXEC xp_dirtree 'C:\Temp' , 1,1 -- does not list anything

--INSERT INTO @files  EXEC xp_dirtree 'C:\Users' , 1,1         --works
--INSERT INTO @files  EXEC xp_dirtree 'C:\Users\Default' , 1,1 --Works
--INSERT INTO @files  EXEC xp_dirtree 'C:\Program Files\Microsoft SQL Server\', 1,1 --works
--INSERT INTO @files  EXEC xp_dirtree 'C:\Users\Donald' , 1,1  --Does not list anything
user918967
  • 2,049
  • 4
  • 28
  • 43
  • It's almost certainly a permissions issue given the folders that you can't access, but I don't have a workaround for you. I just tried adding a domain admin account as a user and created a proc WITH EXECUTE AS ThatUser and it still couldn't see the items in the temp folder. – Russell Fox Jul 24 '17 at 15:54
  • It is not at all clear to me *how* (or which) account I need to add. – user918967 Jul 25 '17 at 08:53

2 Answers2

1

I don't think you can with xp_dirtree, but you can with xp_cmndshell with a little effort. This example is from the "SQL Journey" blog:

DECLARE @FileList TABLE(FileNumber INT IDENTITY, FileName VARCHAR(256))

DECLARE @path VARCHAR(256) = 'dir c:\Temp'
DECLARE @Command varchar(1024) =  @Path + ' /A-D  /B'

INSERT INTO @FileList
EXEC master.dbo.xp_cmdshell @Command

SELECT *
FROM @FileList;
Russell Fox
  • 5,273
  • 1
  • 24
  • 28
  • Doesn't seem to work. Here is the error "Reference to database and/or server name in 'master.dbo.xp_cmdshell' is not supported in this version of SQL Server." I am using SQL Server 2014 – user918967 Jul 31 '17 at 08:51
  • Gotcha - it's disabled by default so you'll need to enable it: https://stackoverflow.com/questions/14550024/why-doesnt-xp-cmdshell-work-in-sql-server-2012 – Russell Fox Aug 01 '17 at 22:02
0

This step works for me.

  1. reboot the server.
  2. create a new folder then rename the same name.
Charlie
  • 11
  • 6