Does sp_list_directory() check whether the specified directory exists?

[VolkerBarth]

I'm guessing the answer is no.

Before unloading to a file I want to assure that the according directory does exist, so I'm using sp_list_directory() for that. (I'm on 16.0.0.2673.)

Simply testing via

select count(*) from sp_list_directory('MyDirectoryPath');

does return 0 both for an empty and an non-existing directory.

Q: Is that by design, or is there a way to make sp_list_directory() return an error for a non-existing "root_path" argument?

---

Aside: The obvious workaround is to check the parent directory if it does contain the desired subdirectory.

[Chris26]

Another workaround seems to be the sp_create_directory() function. This returns 0 (succes) if the directory didn't exist and was created, but also returns 0 if the directory already exists.

[VolkerBarth]

Wow, and that function is even able to create a complete directory tree, i.e. the following does work, too:

select sp_create_directory('C:\\\\NonExistingMainDir\\\\NonExistingSubDir1\\NonExistingSubDir2\\NonExistingSubDir3');

So that helps to make sure the directory tree does exist afterwards... beware wrong arguments:)

[former_SQLA_member1694934]

we create a proxy table in a directory access server then run the procedure below not null result indicates directory exists.

ALTER FUNCTION "owner"."DirectoryConnectionTest"() RETURNS INTEGER BEGIN DECLARE @Result INTEGER;

SELECT COUNT(*) INTO @Result FROM ESTA.Outbox WHERE Permissions NOT like 'd%';

RETURN ( @Result );

EXCEPTION WHEN OTHERS THEN RETURN ( @Result ); END