Solved

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

Posted on 2007-12-06
5
3,369 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

825 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