master.sys.xp_DirTree

Question

I have 25,000 subfolders under parent directory. when i try to run following query it will not display any folders with 25,000 subfolders but i  run with 2000 subfolders it displays all the subfolders. is there any limit on the subfolders to use below query. Any help much appreciated

EXEC master.sys.xp_DirTree 'maindirectorypath’

Thanks,

Answer 1

xp_DirTree is undocumented and unsupported.  You'll need some other way.

Perhaps xp_cmdshell, eg:

exec xp_cmdshell 'dir c:\ /AD /S /B'

David

---

David http://blogs.msdn.com/b/dbrowne/ http://blogs.msdn.com/b/dbrowne/

Answer 2

When i run these it says access is denied.

Answer 3

When i run these it says access is denied.

Hello,

I would suggest you to check which account are used for SQL Server and SQL Server Agent, and ensure the services account have sufficient permission access to your folders. Please take a look at the following similar thread:
http://stackoverflow.com/questions/10822464/access-denied-for-enabled-xp-cmdshell-for-the-admin-user

If you have any feedback on our support, please click here.

Regards, 

---

Elvis Long
TechNet Community Support

Answer 4

This issue got resolved. It was permissions issue on that folder.

Thanks all.

Answer 5

be careful with this command

this : dir c:\ /AD /S /B

might make your server  work hard

try c:\ /ad /b instead

without the /s