CODEWITHSUNDEEP
sql-serverfilet-sqldirectory
user918967
user918967

Reputation: 2167

Using SQL-Server, xp_dirtree does not show files in some directories

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

Upvotes: 0

Views: 8691

Answers (3)

Mokhtar Almohaia
Mokhtar Almohaia

Reputation: 1

The problem clearly about permissions . I fix the same by add local (Users) group as read&Execute/list contents/Read in Security tab (properties)

Upvotes: 0

Charlie
Charlie

Reputation: 11

This step works for me.

  1. reboot the server.
  2. create a new folder then rename the same name.

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5445

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;

Upvotes: 1

Related Questions