HI,
can anyone help me? I am a trying to write a simple SQL script to check whether a file exists of not. I have tracked down xp_fileexist but this only seems to work in a root directory and all my files wil be in subdirectories... is there another command I am missing or is it not possible?

I don't understand. Can you post what you've got so far? SQL is for querying and modifying databases - is this what you mean and if so can you outline your db structure?

What I have is a PDF path in a db record, for some reason a PDF hasn't been created and I am trying to track down easily a list of the missing PDFs,

so basically what I am trying to do is below in 'english'..

select * from table T1
where T1.PDF_Path doesn't exist

My understanding is that the functionality isn't there. Yes you need the SQL to read the file names / paths out of the database, but there's no way to get SQL to loko through the file/folder structure. Any other programming language should be fine though.

You could try the xp_cmdshell command. You could then have this fire off a script which would allow you to use all the usual UNIX/Windows command line tools to search for files...

thanks guys, I was kinda figuring I was asking the impossible... guess i wil have to write a program to do it...

It sounds perfectly scriptable, as Ben says.

You need a script that:

1. Executes the SQL to get the file paths
2. Grabs the results from the SQL
3. For each file path
   
   1. Browses to see if the file exists
   2. If it doesn't add it to an output file

I don't have much experience of Windows scripting, but it'd be doable in Bash for example.

bash, perl, a quick c# command line tool

sql wont help find the file, its a db language, you will just get the file name (possibly path)

Yup. My understanding was there's a DB with file paths in it somewhere, that needs querying first.

that is correct, xp_fileexists only works to a single level. I am, going to write a program in the app, it wil take a lot longer but that's the only solution with my limited sql knowledge.

thanks for the help everyone, I may revisit this when I have sometime to look into it properly...

That is all.