Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-12-06
5
Medium Priority
?
3,828 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 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