get full path from sys.xp_dirtree

Question

get full path from sys.xp_dirtree

Viewing 6 posts - 16 through 20 (of 20 total)

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

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

Jeff, thank you for pointing perfomance issue out. I would also note that it would be so simple for MS to add parent reference to the sp_dirtree output thus eliminating half of the problem.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Still, it's a brilliantly simple solution and it does solve a very common problem without a trip to the Command Prompt. Well done, again!

And, yeah, I agree. How difficult would it have been for them to include the full parent path, the size, and the 3 dates associated with each file? Not everyone in the world wants to use SSIS for ETL. 😀

--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)

andy.johnston SSC Rookie Points: 34 More actions · Answer 3

serg-52 - Monday, December 8, 2014 4:55 AM
This batch will show all objects (directories, files) and their full paths--parameterdeclare @myPath nvarchar(4000) = 'E:\ElectronicArchieve';IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULLDROP TABLE #DirectoryTree;CREATE TABLE #DirectoryTree (id int IDENTITY(1,1),subdirectory nvarchar(512),depth int,isfile bit, ParentDirectory int,flag tinyint default(0));-- top level directoryINSERT #DirectoryTree (subdirectory,depth,isfile)VALUES (@myPath,0,0);-- all the rest under top levelINSERT #DirectoryTree (subdirectory,depth,isfile)EXEC master.sys.xp_dirtree @myPath,0,1;UPDATE #DirectoryTreeSET ParentDirectory = (SELECT MAX(Id) FROM #DirectoryTreeWHERE Depth = d.Depth - 1 AND Id < d.Id)FROM #DirectoryTree d;-- SEE all with full pathsWITH dirs AS ( SELECT Id,subdirectory,depth,isfile,ParentDirectory,flag , CAST (null AS NVARCHAR(MAX)) AS container , CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath FROM #DirectoryTree WHERE ParentDirectory IS NULL UNION ALL SELECT d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag , dpath as container , dpath +'\'+d.[subdirectory] FROM #DirectoryTree AS d INNER JOIN dirs ON d.ParentDirectory = dirs.id)SELECT * FROM dirs -- Dir style orderingORDER BY container, isfile, subdirectoryhope it helps.

Hi serg-52, just asking if you could point me in the right direction with the code you posted for the above thread. I've managed to use the code and create a list of files in shared drive with associated paths and works great thanks. What I need is to set up in SQL is a table that is constantly updated and reflects any new files added to the shared area [immediately]. I am not sure how to take your code [with it being a batch of commands] to do this in the most effective / efficient way; stored procedure, view, in the master DB or the usual database I use, if it can be done / bad idea? Apologies I am only use to creating Tables / views and read / writing from excel [vba]. I have a document management system in excel that reads the directory using VBA but it takes too long to update, and your solution is very fast. Many thanks in anticipation. Andy Johnston

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

andy.johnston - Wednesday, June 21, 2017 3:11 AM
Hi serg-52, just asking if you could point me in the right direction with the code you posted for the above thread. I've managed to use the code and create a list of files in shared drive with associated paths and works great thanks. What I need is to set up in SQL is a table that is constantly updated and reflects any new files added to the shared area [immediately]. I am not sure how to take your code [with it being a batch of commands] to do this in the most effective / efficient way; stored procedure, view, in the master DB or the usual database I use, if it can be done / bad idea? Apologies I am only use to creating Tables / views and read / writing from excel [vba]. I have a document management system in excel that reads the directory using VBA but it takes too long to update, and your solution is very fast. Many thanks in anticipation. Andy Johnston

Andy, your problem is known as a Folder Watcher and it's rather operating system related. Sql Server has no tools to watch file system activity.
See this http://devproconnections.com/net-framework/how-build-folder-watcher-service-c for c# example, this could be really fast and the table in question can be updated in real-time . Alternatively you can schedule the above script with Sql Server Agent to run say every 10 minutes, provided 10 min delay is OK. See https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

serg-52 - Wednesday, June 21, 2017 2:33 PM
andy.johnston - Wednesday, June 21, 2017 3:11 AM
Hi serg-52, just asking if you could point me in the right direction with the code you posted for the above thread. I've managed to use the code and create a list of files in shared drive with associated paths and works great thanks. What I need is to set up in SQL is a table that is constantly updated and reflects any new files added to the shared area [immediately]. I am not sure how to take your code [with it being a batch of commands] to do this in the most effective / efficient way; stored procedure, view, in the master DB or the usual database I use, if it can be done / bad idea? Apologies I am only use to creating Tables / views and read / writing from excel [vba]. I have a document management system in excel that reads the directory using VBA but it takes too long to update, and your solution is very fast. Many thanks in anticipation. Andy Johnston
Andy, your problem is known as a Folder Watcher and it's rather operating system related. Sql Server has no tools to watch file system activity.
See this http://devproconnections.com/net-framework/how-build-folder-watcher-service-c for c# example, this could be really fast and the table in question can be updated in real-time . Alternatively you can schedule the above script with Sql Server Agent to run say every 10 minutes, provided 10 min delay is OK. See https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job.

It sure does look like this was made simpler in .NET than it used to be. I wrote one years ago in VB 5 and had to use the Win32 API for nearly everything that mattered. I think it's good that they're exposing more OS functionality through libraries. I guess the only downside is that we don't need to have as thorough of an understanding to make it work. On the other side of the coin, we can't commit so many errors. 😛

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

andy.johnston SSC Rookie Points: 34 More actions · Answer 6

Many thanks for pointing me in the right direction and helping me solved my problem.

Solution I used: Created an "SQL Stored Procedure" to update a table [not temp table] with the basic file information in the database. Then created an "SQL View" that used this data to add / created the path and path with file name. Then triggered the "SQL Stored procedure" from Excel VBA; user can updated when they want / on certain events in the spread sheet. ODBC link to the "SQL View" to keep the spread sheet up to date with the files in the shared folder.

This now updates the file list in about 4-5 seconds as opposed to 100 seconds+ using the original VBA.

Used your code as a great starting point serge-52. Will have a look at the Folder-Watcher option going forward; but may be a bit too "techy" for me.

Thanks again. Andy