Solved

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

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 39
Powershell v3 - SQLCMD 3 26
Need help in debugging a UDF results 7 13
MS SQL Delete Duplicate Rows Only 2 9
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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 ?
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

792 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