Check if a file exists or not?

[former_SQLA_member1694869]

Today I use xp_read_file to see if a file exists or not. But on somtimes it return null even if a file exist?

if xp_read_file('\\\\\\server10\\\\folder1\\\\test.log') is null then 0 else 1 endif;

I guess there's better way than do like this to check if a file exist or not?

[VolkerBarth]

When you do use xp_read_file() - say, because you are not running v16 or above - you can set the optional 2nd "lazy" parameter to 1 or the like to prevent the file contents from being read. That should give a performance benefit particularly for larger files.

[former_SQLA_member1694869]

We use v12. What then is the best method to check if a file exists or not with that version. xp_read_file works fine, but many times it returns null even if it is a physical file on that path?

[Breck_Carter]

Sounds like a bug, unless perhaps it is returning null when the file is empty... " If the file does not exist or cannot be read, NULL is returned." - V16 Help

[former_SQLA_member1694869]

Strange. Is there a bugfix made between v12 and v16?

[VolkerBarth]

Note the "or cannot be read" part - possibly the according file is opened in exclusive mode?

E.g. trying to open the database's own translog will return NULL although it certainly exists at that time:

select db_property('LogName'), xp_read_file(db_property('LogName'));

(And according to your sample, the same will be true if you try to open the log of another database...)

[former_SQLA_member1694869]

I do not quite understand what you mean. If I want to see if a file exists or not, whether it is open or not, what do I do? Your example only returns null for me.

[justin_willey]

If the files you are looking at may be open / locked you need to look at the other two solutions proposed below: ie a directory server, or simpler if you are using v16 / v17 sp_list_directory()

xp_read_file() does what it says - it reads the file. If the operating system won't let the database engine read the file, it can't read it and therefore can't return anything.

Also be aware that xp_read_file() by default reads the whole file, so if the files that you are interested in are very large, it could be slow and generate a lot of disk activity. It that case you can choose to read just one byte of the file with something like:

select byte_substr(xp_read_file(<filepath>,1),0,1)

which will return just the first byte of the file - enough to know if there is something there!

[former_SQLA_member1694869]

Ok, I think the problem is that the operating system won't let the database engine read the file. There we have the answer! So, I use v12, is there any other way to look if a file exist or not?

[justin_willey]

Thiago's solution will work in v12 - see the docs on CREATE SERVER

http://dcx.sap.com/index.html#1201/en/dbreference/create-server-statement.html

If you don't want to do that for whatever reason I suggest you think about using a batch file with an construct like:

 IF EXIST filename ... 

that set an error level accordingly and call that with xp_cmdshell() and look at the return values.

[former_SQLA_member1694869]

Big thanks! Something like this?

select xp_cmdshell('if not exist c:\\test.txt goto error')

Returns 1 since goto error returns an error since error does not exist.

select xp_cmdshell('if exist c:\\test.txt goto error')

Returns 0 since c:\\test.txt exists.

[former_SQLA_member1694869]

Regarding Thiago solution, I guess I should change this row to include subfolders

create server backup_tree class 'directory' using 'root=L:\\LOG';subdirs=y

[former_SQLA_member1694869]

Doesn't cmdshell work on a network path? Locally on the C:\\ it works for me, but when I try on a mapped drive, it will not work. Ex:

 select xp_cmdshell('if not exist H:\\\\test.txt goto error', 'no_output')

[justin_willey]

It depends on the rights of the user that the database engine runs under. The default system user won't have rights to network locations. You'll need to configure network rights appropriately whichever method you use.

[former_SQLA_member1694869]

Thanks for fast response!

Can you describe a little more how I should tell our IT provider how to configure the database engine so it get network rights?

[VolkerBarth]

You may have a look at that FAQ, both as to the background and possible solutions:

How do I execute a batch file using xp_cmdshell?

---

BTW: Is that the reason why xp_read_file() does fail for you - the engine is asked to read a file on a net share that it cannot access? Or is the file locked exclusively? What do you want to do with the file if it exists?

[VolkerBarth · Accepted solution]

Just for completeness I'd like to add a further solution (although certainly not an "easy one", so I would prefer the directory access server for v12):

You can also create an external (native) function that calls an OS-level function to check for an existing file, say for Windows to call the GetFileAttributes API.

Breck has explained in his (former?) blog how to call a different WinAPI function (GetOpenFileName()) here:

Calling GetOpenFileName() From SQL

[Breck_Carter · Accepted solution]

Are you saying that doesn't pass the "something simpler" test? 🙂

[ThiagoReis]

SELECT 1 FROM sp_list_directory( '\\\\\\\\server10\\\\folder1\\\\' ) where file_path = '\\\\\\\\server10\\\\folder1\\\\test.log'

[VolkerBarth]

Just to note: sp_list_directory() was introduced in v16.

[ThiagoReis]

I have this example that I use to check my backup logs:

 create server backup_tree class 'directory' using 'root=L:\\LOG';
 create externlogin dba to backup_tree;
 create existing table backup_files at 'backup_tree;;;.';

After that, to check a file, you can just run a simple select:

 select file_name from backup_files;

Your directory will work like a table. You can select, update and delete files. It works for me.

[former_SQLA_member1694869]

Thanks! But there must be something simpler way to do it?

[former_SQLA_member1694869]

Can I use this to include subfolders? Or Can I load two different folders to same table?