Solved

How to delete all files in a directory using a stored proc

Posted on 2004-03-24
5
345 Views
Last Modified: 2008-02-01
i'm new to sql and need some assistance with the following


 I have a stored procedure that outputs a bunch of .csv log files into a log directory. d:\logs\  

I need a script that i can add to the begining of my stored proc to delete all the csv files in d:\logs  before running the rest of the script.  Also, it needs a check... if there are no files in d:\logs then to continue with the script.

in the begining of my stored proc:
1. check to see if there are any files in d:\logs
2. if so delete them all, if not then continue with the script

I've read about the xp_cmdshell to do this and dts but how do i put that into my stored proc.  I need an example
 
thanks,

Lat



 
0
Comment
Question by:latzo2
[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
  • 3
5 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 50 total points
ID: 10670835
In your stored proc:

master..xp_cmdshell 'del d:\logs\*.csv'



0
 
LVL 4

Accepted Solution

by:
squatex earned 75 total points
ID: 10671000
create table #c (name varchar(200))

insert #c exec('master.dbo.xp_cmdshell ''dir C:\path\to\files\*.csv /b''')
if((select count(*) from #c) = 0) --ther are no files we care about in this folder
begin
--do stuff here
end
else
begin
--delete the files
master.dbo.xp_cmdshell 'del c:\path\to\files\*.csv'
end

drop table #c
0
 
LVL 34

Expert Comment

by:arbert
ID: 10671176
I don't understand.  Why do you need to even check the content of the folder for files--you're just going to turn around and delete any CSVs (if they exist) and then continue on with the job anyway.  So, just issue the delete command and continue on....
0
 

Author Comment

by:latzo2
ID: 10671928
i found out how to do this on sqlcentral.  

DECLARE
      @folder1 varchar(10),
      @folder2 varchar(10),

SET @Folder1 = 'c:\log1\'
SET @Folder2 = 'c:\log2\'
SET @deletefiles = 'del /Q ' + '"' + @Folder1 + '*.*' + '"'
set @deletefiles1 = 'del /Q ' + '"' + @Folder2 + '*.*' + '"'
exec master..xp_cmdshell @deletefiles
exec master..xp_cmdshell @deletefiles1

your right arbert, basically i just want to execute the delete command, however with your given statement, if there is no log files in the directory it comeback with "The system cannot find the file specified." and the rest of the script stops.  the script i found seems to work fine.

thanks

Lat
0
 
LVL 34

Expert Comment

by:arbert
ID: 10672686
The only difference is the /Q for quiet.....
0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

630 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