How to check for existance of a text file. 1

[MarkButler]

I want to check for the existance of a text file, the file name being passed to a stored procedure. If the file exists inport it and archive it, if not do an early exit.

Do I just need to act on it as though it exists and catch the error or is there a cleaner way?

Thanx
Mark

 

[NathanGriffiths]

I think you can use the undocumented stored procedure

sp_MSexists_file file_path, filename

e.g. EXEC sp_MSexists_file 'C:\temp\', 'Myfile.txt'

Returns 1 if file exists, otherwise returns 0

Have a look at this link for more details;

http://www.databasejournal.com/features/mssql/article.php/1441231

Nathan

 

[NathanGriffiths]

Correction; For SQL Server 2000 the command is

sp_MSget_file_existence file_path_and_name

e.g. sp_MSget_file_existence 'c:\temp\myfile.txt'

I haven't been able to get this to work with UNC file paths though (e.g. \\MyComputer\MyComputer_C\temp\myfile.txt), so the files will need to be on a mapped network drive to the server.

Hope that helps




Nathan

 

[MartinCroft]

Hi, this might be of interest, then again it might not

if you run

sp_helptext 'sp_MSget_file_existence'


it shows exactly what the proc does to test for file existance.

Regards

mysticmart