• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

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
0
PhilippeRenaud
Asked:
PhilippeRenaud
1 Solution
 
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
 
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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
 
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:
aneeshattingal,
Everything I posted was a result of testing in my environment and my machine is a client.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now