Solved

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

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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 …
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now