Solved

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

Posted on 2004-03-24
5
339 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
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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