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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.
Suggested Courses

764 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