Posted on 2003-03-31
Medium Priority
Last Modified: 2008-02-01
I am trying to use xp_fileexist to see if a file a exist by using (*) in a specific directory.  If I use the file name, it works fine.  If I use *.txt, or test.*, it does not find the file.

EXAMPLE:  I would like to get a print out of the following files.
   1) test1.txt
   2) test2.txt


I tried the following:
EXEC master..xp_fileexist '\\MyServerName\MyDirectory\*.txt'
EXEC master..xp_fileexist '\\MyServerName\MyDirectory\*.*'

Any suggestion?????
Question by:th34
  • 2
  • 2
LVL 14

Expert Comment

ID: 8240879
Here's a procedure I have used to return a dir list to a file.  you could pass in your file name with wildcards.  with some modifications, this could work for you.

CREATE PROCEDURE dbo.get_dir_file_list
   @folder   varchar(6000),
   @Subfolders   BIT = 0 --Lists the folders too

declare @error         INT,
        @script        varchar(8000),
        @file_name     varchar(8000)

SET @script = 'cd ' + @folder
EXEC @error = master..xp_CMDSHELL @script, NO_OUTPUT

IF @error = 0
   SET @script = 'DIR ' + @folder + ' /b /o:g /o:n /a:-d '
   If @Subfolders = 1
      SET @script = @script + '/s '
   SET @script = @script
   CREATE TABLE #path(Path VARCHAR(8000))

   INSERT #path(Path)
   EXEC @error = master.dbo.xp_cmdshell @script

   SELECT * FROM #path

   DROP TABLE #path

--exec get_dir_file_list 'C:\temp', 1

Author Comment

ID: 8240935
Can you explain something to me. If I run EXEC master..xp_fileexist '\\MyServerName\MyDirectory\*.txt',
is sql looking at "*.txt" as a literal string?

If I was only looking for "test1.*", Do I still need to write a procedure just to look for 1 file?
LVL 14

Accepted Solution

adwiseman earned 200 total points
ID: 8240968
'xp_fileexist ' is an undocumented extended stored procedure.  I would infer from your test which I tried, that it is looking at the * as a literal character.

You could change my proc to return a 1 or a 0 depending on if 0 records or more than 1 record is returned.

Author Comment

ID: 8241041
can I do something like this?
EXEC EXEC master..xp_fileexist '\\MyServerName\MyDirectory\dir *.dir'

Is is something you recomend?

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question