Set the max_depth parameter to 1 in the sp_list_directory() call if you are using SUBDIRS=0 in the CREATE SERVER statement.

Use SELECT TOP 1 and sa_split_list() to get the file name out of the file_path column returned by sp_list_directory().

Use LATERAL to join the sp_list_directory() and sa_split_list() calls.

By Example

After several years of intermittent errors, the SERVER CLASS 'DIRECTORY' method was recently judged too fragile for further use in Foxhound. Here is the before-and-after code for the four sections that were changed: