Solved

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

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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

752 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