[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2007-12-06
5
Medium Priority
?
3,727 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
[X]
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
  • 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

656 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