xp_dirtree

Question

xp_dirtree

MrBen

Old Hand

Points: 341
More actions
February 6, 2024 at 4:53 pm

#4374291

Hello.
I'm using sys.xp_dirtree to get a list of files in a folder.
exec master.sys.xp_dirtree '\\myServer\Users\Documents\BulkInsert\', 1, 1;
This was working fine whilst the number of files was approximately 3000. Now the number of the files has double but xp_dirtree is only returned the circa 3000 files.
I changed the statement to :-
exec master.sys.xp_dirtree 'c:\Users\SQLAdmin\Documents\\BulkInsert\', 1, 1;
and all 6000 files are being returned.
Any ideas why using a UNC path is somehow problematic?
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Are you absolutely sure that the drive path and the UNC are actually pointing to the same directory? I HAVE seen people get confused on such issues before.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike01 SSChampion Points: 11395 More actions · Answer 2

looks like 2 different folders?? Should the first one be \\myServer\Users\SQLAdmin\Documents\BulkInsert\??

exec master.sys.xp_dirtree '\\myServer\Users\Documents\BulkInsert\', 1, 1;

exec master.sys.xp_dirtree 'c:\Users\SQLAdmin\Documents\\BulkInsert\', 1, 1;

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mike01 SSChampion Points: 11395 More actions · Answer 3

posted twice

This reply was modified 9 months, 2 weeks ago by Mike01.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

MrBen Old Hand Points: 341 More actions · Answer 4

Hi Mike.

Sorry, but yes you are correct the file paths in my example are not identical. I did a bad job of editing them. They should be :-

exec master.sys.xp_dirtree '\\servername\Users\SQLAdmin\Documents\BulkInsert\', 1, 1;

exec master.sys.xp_dirtree 'c:\Users\SQLAdmin\Documents\BulkInsert\', 1, 1;

I'm thinking about just writing a CLR for this as ideally I want to get the file creation too. Just curious why there appears to be a difference with c:\ and UNC.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

MrBen wrote:

Just curious why there appears to be a difference with c:\ and UNC.

It depends on where thing are at and where you're running the command from. For example, if the UNC path (which can be a share from half way across the world) isn't actually on the C: drive of the server that you're running the command from, the directories are actually different, totally separate directories.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

I would recommend moving that 'share' to a different location - and not on the C:\ drive of the SQL Server instance. The location you have listed is also considered a 'special' folder in Windows - and could be redirected to a different location under the covers.

Before trying to build a CLR - try moving the files to a specifically created location. If you have another drive - that does not contain database files available on the server, then create a folder on that drive and share it. If not, then create a new folder on the C:\ drive just for these files - copy/move the files into that location.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs