SQL see if file exists

Hello EE,

i have a folder where it could contains multiple files...
something like:


EE001.doc
abc.pdf
EE002.doc
EE001.pdf

How can I know if a file or more exists without giving the extension ?

I know there is exec master.dbo.xp_fileexist [@stringname]
but i need to put extension...

here in my example I would like to see that if I search with the name EE001 well it exists because there is a .doc and a .pdf

any idea ? I tried exec master.dbo.xp_fileexist "EE001.*"  but does not work
LVL 1
PhilippeRenaudAsked:
Who is Participating?
 
jonnidipCommented:
You can use a cmdshell rather than fileexist:
exec master.dbo.xp_cmdshell 'dir c:\EE001.* /b'

Open in new window


Regards.
0
 
Jared_SCommented:
exec master.dbo.xp_fileexist 'C:\Documents and Settings\me\Desktop\EE001.doc'

Should find EE001.doc on your desktop.

The stored procedure does run under the sql user's account, so you may run into problems if you don't have the right security credentials on the network.

Here is a post explaining a work-around that you can try if you need to.

http://www.sqlservercentral.com/Forums/Topic533876-146-2.aspx
0
 
PhilippeRenaudAuthor Commented:
Yes but if I dont type at the end the .doc how can I search the file ?

my question was lets say I dont know the extension is it possible to search with a .* or % for many
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Jared_SCommented:
I'm sorry, I completely mis-read your question.

Just leave off the extension:

exec master.dbo.xp_fileexist 'C:\Documents and Settings\me\Desktop\EE001'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You need to mention typical 8.3 naming conversion

exec master.dbo.xp_fileexist 'C:\Docume~1\me\Desktop\EE001.doc'

try something on C:\

exec master.dbo.xp_fileexist 'C:\EE001.doc'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
also, xp_fileExist checks for that file on the server where you installed the sql server and wont check on your local machine. for searching files on the network drives, your sql server service account should have sufficeint permission to see the content of that network folder
0
 
Jared_SCommented:
aneeshattingal,
Everything I posted was a result of testing in my environment and my machine is a client.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.