Solved

SQL Stored Procedure - Find if a .txt file exists using wildcard

Posted on 2007-12-06
5
3,278 Views
Last Modified: 2008-02-01
Hello,

Does anyone here knows how to check whether if any files with a particular extension exists in a folder?  

I'm building a stored procedure (SQL Server 2000) where I'm supposed to find any files with the .txt extension in one of my hard drive folders.  Here's the code I'm using:

DECLARE @Var varchar(200)
DECLARE @WildCard varchar (50)
SET @WildCard = 'MyLostFile'

SET @Var= 'C:\MyFolder\' + @wildcard + '.txt'

EXEC master..xp_fileexist @var, @return OUTPUT

IF @return =1
BEGIN
   PRINT'File does exist  '
END
ELSE
BEGIN
   PRINT 'File does not exists   '
END
GO

--------------------------

This works like a charm if I provide the actual name of the text file in the wildcard variable.  However, I am not supposed to know it.  I need the wildcard variable to be a truly wild card like '%' and then I'm supposed to look in my folder for any files with the .txt extension.

If anyone knows how to accomplish this, please share it.

Thanks!


0
Comment
Question by:TheUndecider
  • 4
5 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 20424574
Are you sure that
SET @Var= 'C:\MyFolder\*.txt'
does not work?
0
 
LVL 29

Expert Comment

by:QPR
ID: 20424587
sorry I should have tested before posting - u r right doesn't work :(
0
 
LVL 29

Expert Comment

by:QPR
ID: 20424602
try this then check to see the value of @i


declare @i as integer
exec @i = master..xp_cmdshell 'dir c:\myfolder\*.txt /b'
print @i
0
 
LVL 19

Expert Comment

by:weellio
ID: 20424611
maybe?
Declare @Filename varchar(1000)

Declare @cmd varchar(1000)

Create table #dir (Filename varchar(1000))

Insert #dir

Exec master..xp_cmdshell 'dir /B C:\MyFolder\*.txt'

delete #dir where Filename is null or Filename like '%not found%'

Select @Filename = ''

While @Filename < (select max(Filename) from #dir)

Begin

   Select @Filename = min(Filename) from #dir where Filename > @Filename

   /*do stuff here*/

end

drop table #dir

Open in new window

0
 
LVL 29

Accepted Solution

by:
QPR earned 500 total points
ID: 20424636
An improvement on my previous post which will output what you originally asked for...

declare @i as integer
exec @i = master..xp_cmdshell 'dir c:\*.txt /b'
if @i <> null
begin
print 'File does exist'
end
else
begin
print 'file does not exist'
end
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now