SA 12.0.1

Is it possible through a procedure to confirm whether a file exists in a local directory? I have come across a few methods in SQL Server, but nothing for SA.

asked 29 Jan '14, 16:26

spc's gravatar image

spc
1699918
accept rate: 0%


There are several ways of doing this... but the easiest is perhaps to just try to read it using xp_read_file() and check if you got any result (and did not get an error). This might be a bit of an overkill if the file is large (see note below). Another drawback is that you must have permissions to read the file... so if you just want to check for existence but not necessarily have read permissions then xp_read_file cannot do this.

If xp_read_file is not a solution then you might want to consider using a directory access server / proxy table. E.g. you could create an proxy table to your entire disk and then check for the existence of the file by simply 'select'ing the (for example) size of the file from the proxy table.

In version 16 you could use the new sp_list_directory() function to check for the existence of a file within the directory.

If you don't want to do any of the above then you could choose to use xp_cmdshell to spawn a command that checks for the existence of the file and returns yes/no as a return code (or writes a file with the answer and then use xp_read_file) to get the answer - this is not very elegant so I would not recommend it.

Finally, you could write an external procedure using one of the external environment languages (C/C++, .Net, Perl, PHP, Java) and use that procedure to test for the existence of the file. If using C/C++ you could also use the external procedure 'in process' rather than using an external environement.

HTH


In SQLA v12 an enhancement was made to xp_read_file that allows the file to be read lazily. This is done by specifying a second non-zero parameter in the call to xp_read_file. When lazy reads are done the file is not actually read until it is needed... so using xp_read_file for checking for existence is not so bad! See the documentation for more info.

permanent link

answered 29 Jan '14, 17:07

Mark%20Culp's gravatar image

Mark Culp
22.6k9129265
accept rate: 40%

edited 29 Jan '14, 17:17

I went with the xp_read_file method. Seems to be working when I apply the 'lazy' argument.

(30 Jan '14, 12:52) spc
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:

×20

question asked: 29 Jan '14, 16:26

question was seen: 3,298 times

last updated: 30 Jan '14, 12:52