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.

asked 25 Jul, 04:31

Volker%20Barth's gravatar image

Volker Barth
32.2k328474688
accept rate: 32%

edited 25 Jul, 04:38

2

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.

(25 Jul, 05:23) Christian Ha...
Replies hidden
1

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

(25 Jul, 05:31) Volker Barth

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

permanent link

answered 01 Sep, 12:25

JJ%20Diaz's gravatar image

JJ Diaz
714
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×230

question asked: 25 Jul, 04:31

question was seen: 88 times

last updated: 01 Sep, 12:25