This extended and undocumented stored procedure can be used to list all folders and subfolders (recursively) from a given directory.
XP_DIRTREE has three parameters:
- Directory – This is the directory you pass when you call the stored procedure; for example, ‘D:\Backup_Files’.
- Depth – This tells the stored procedure how many subfolder levels to display. The default of 0 will display all subfolders.
- Files – This will either display files as well as each folder. The default of 0 will not display any files.
XP_DIRTREE is an undocumented and unsupported command. At times when the servers reboot the underlying DLL file doesn’t register and could cause xp_dirtree to not work.
Here are some ways how you can troubleshoot this and make it work:
- Reboot the server where the XP_DIRTREE issue is happening (This will need to be coordinated with teams using the server)
- If #1 doesn’t work, then one could use non-SQL code or CLI options like CMD/PowerShell to browse through the files as XP_DIRTREE is a functionality of SQL Server that is not supported or documented.
Call a PowerShell script from SQL
ls <SourceFolder*> | Select-Object NAME | Sort-Object LastWriteTime | foreach{if(-not [System.IO.File]::Exists(''<ArchiveFolder*>\''+ $_.Name)){$_.Name} } | out-File -FilePath <ArchiveFolder*>\ProcessingFiles.txt
- If multiple servers are involved in the process, make sure the SPNs are configured correctly.
Please note: Raising an MSFT case may not help given that XP_DIRTREE is not supported or undocumented.
When Microsoft had an agreement to share the Sybase SQL Server source code years back, and Microsoft remarketed it as “SQL Server” on the PC platform, XP_DIRTREE was one of the primitive commands SQL Server inherited.
Alternatives of XP_DIRTREE:
- XP_CmdShell is available on all versions of SQL Server but it is a risky operation to use, in terms of security and server resources.
- You can also use PowerShell or SMO.
