Delete Files from directory - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete Files from directory

Author

Topic

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-15 : 16:33:18

Hi all,
how can I delete files, which are allready imported into a database. I have a folder with 600,000 files . All Files should be in a database. And I want to delete the files that are found in the table with filename . How can I make the ?? The Filename and physicals Name is the same so i can say table.filename=physical.filename.

Kristen
Test

22859 Posts

Posted - 2014-11-16 : 03:48:49

One off task?

if so I would make a BATCH file and execute that manually:


SELECT 'DEL "' + filename + '"' FROM table

save the output to MyFile.BAT and execute it on the server (IN the appropriate folder)

For an ongoing automated task then you could run a SQL script that deletes files direct, but if it deletes every filename, based on every row in your Table, every time in executes then that will be a lot of activity (presuming the files will not exist after the first time).

Alternative is that the script makes a Directory Listing of the actual files on disk, compared that against the filenames in Table, and then only deletes those.

If a file is NEVER recreated, a second time, then it might be sufficient to have a Flag column in Table that indicates whether Filename has been deleted, or not, and then just send a DELETE command for any files that are flagged (and update the Flag to indicate the file has been deleted). We use this to delete old Backup files (after a retention period has expired) but we sometimes have failures which mean we get left with Orphan files on the disk - because the backup files are in chronological order it is easy to see that a few files are "very old" and delete them manually. It probably only happens after we restore some files from tape (which have already been flagged as Deleted in the database)

We use:


EXEC master.dbo.xp_fileexist @MyFileName, @intErrNo OUTPUT

to detect if the file exists (note that @MyFileName includes a full path to the file, from the server's perspective!)

If @intErrNo = 1 the file exists and we delete it:


SELECT @MyCommand = 'DEL "' + @MyFileName + '"'
EXEC @intErrNo = master.dbo.xp_cmdshell @MyCommand

Note that, for security, xp_cmdshell is not enabled in MSSSQL by default.

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-21 : 03:19:47

Hi Kristen,

sorry for my late replay.
The delete Task should run daily and not only one time. I do not understand your guide. Can you describe it in detail?

Thx
zero1de

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 09:07:12

Dear Zero,

Kristen DID give a detailed explanation. Please indicate which step or steps you're having trouble with.

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-24 : 12:54:54

Hi Bgritton, Hi Kristen,

i do not have only one file to compare. In first step i must check 600,000 files because the filename compare never runs until now. However, the procedure should run and check files every day if they are imported to the table and delete them from directory. I have not much experience with coding :(

Many thx for your help guys.

Kr
zero1de

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 13:49:47

Kristen gave a general approach that should work in your case. Whether 6 files, 600,000, or 6 billion. Let us know what step you're struggling with, and we'll dig into that with you.

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-27 : 07:39:07

Hi,

I have adapted the query to this one. But the procedure delete every file *.doc in Forder C:Data?
I want, only delete Files in C:data which exist in the table tbl_france Colum. What runs here wrong??

DECLARE
@image_files TABLE (file_path VARCHAR(MAX))

DECLARE
@file_path VARCHAR(MAX),
@cmd VARCHAR(MAX)

INSERT INTO @image_files (file_path)
EXEC xp_cmdshell 'dir C:\Data\*.doc /b /s /x'

DECLARE file_cursor CURSOR FOR
SELECT
file_path
FROM
@image_files
WHERE
file_path NOT IN
(
SELECT QC_DESCRIPTION
FROM
tbl_France
)

OPEN file_cursor
FETCH NEXT FROM file_cursor INTO @file_path
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @cmd = 'EXEC xp_cmdshell ''del ' + @file_path + ''''
EXEC(@cmd)

FETCH NEXT FROM file_cursor INTO @file_path
END

CLOSE file_cursor
DEALLOCATE file_cursor

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 08:48:59

Your where clause:


WHERE
file_path NOT IN
(
SELECT QC_DESCRIPTION
FROM
tbl_France
)

is selecting files that are NOT in tbl_france. Perhaps you need to remove the NOT?

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-27 : 09:58:38

Hi Gbritton,

In first step I have thought also, however, it makes no differenc if you use NOT IN or IN clause. Moreover, there query runs longer with the same result.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 10:22:25

If you compare the outputs of the query with NOT IN vs IN, there will be a difference. Start with that. It's more important that your script is correct than that it runs longer or shorter.

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-27 : 10:33:08

Yes you are right but the result is the same.
And I have also the problem in table value which has filename without extension and path. And xp_cmd command reads Path+Fileextension into the compare table so it's not easy to compare

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-27 : 11:15:03

IN the body of your loop, you build a command from the FETCH on the cursor. Since IN and NOT IN produce different results, the loop will run on different data and the command executed will be different. UNLESS, the command built in the loop has wildcards in it.

Also, since you know that the file extension is ".doc" -- just four characters long, you can chop off the last four easily:


declare @filenameonly varchar(255) = left(@filename, len(@filename) -4))

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-28 : 03:47:44

something goes wrong here. The query runs without error msg and every time i get the same msg after run: "2 row are affected" But this can't be true it must be much more.

Last version:
DECLARE @image_files TABLE (file_path VARCHAR(MAX))
DECLARE @file_path VARCHAR(MAX), @cmd VARCHAR(MAX)

INSERT INTO @image_files (file_path)
EXEC xp_cmdshell 'C:\Data\*.doc /b /s /x'

DECLARE file_cursor CURSOR FOR
SELECT file_path FROM @image_files
WHERE file_path IN
(
select 'C:\Data\' + QC_DESCRIPTION +'.doc' from tbl_France where QC_DESCRIPTION is not null
)

OPEN file_cursor

FETCH NEXT FROM file_cursor INTO @file_path

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @cmd = 'EXEC xp_cmdshell ''del ' + @file_path + ''''
EXEC(@cmd)

FETCH NEXT FROM file_cursor INTO @file_path
END

CLOSE file_cursor
DEALLOCATE file_cursor

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-28 : 05:29:46

quote:
Originally posted by zero1de

something goes wrong here. The query runs without error msg and every time i get the same msg after run: "2 row are affected" But this can't be true it must be much more. And
Output "The system cannot find the path specified".

Last version:
DECLARE @image_files TABLE (file_path VARCHAR(MAX))
DECLARE @file_path VARCHAR(MAX), @cmd VARCHAR(MAX)

INSERT INTO @image_files (file_path)
EXEC xp_cmdshell 'C:\Data\*.doc /b /s /x'

DECLARE file_cursor CURSOR FOR
SELECT file_path FROM @image_files
WHERE file_path IN
(
select 'C:\Data\' + QC_DESCRIPTION +'.doc' from tbl_France where QC_DESCRIPTION is not null
)

OPEN file_cursor

FETCH NEXT FROM file_cursor INTO @file_path

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @cmd = 'EXEC xp_cmdshell ''del ' + @file_path + ''''
EXEC(@cmd)

FETCH NEXT FROM file_cursor INTO @file_path
END

CLOSE file_cursor
DEALLOCATE file_cursor

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-28 : 05:57:22

quote:
Originally posted by zero1de

quote:
Originally posted by zero1de

something goes wrong here. The query runs without error msg
but i get this output ?

output
======
Could Not Find C:\Data\Integration
Could Not Find C:\Windows\system32\Lettre

Last version:
DECLARE @image_files TABLE (file_path VARCHAR(MAX))
DECLARE @file_path VARCHAR(MAX), @cmd VARCHAR(MAX)

INSERT INTO @image_files (file_path)
EXEC xp_cmdshell 'C:\Data\*.doc /b /s /x'

DECLARE file_cursor CURSOR FOR
SELECT file_path FROM @image_files
WHERE file_path IN
(
select 'C:\Data\' + QC_DESCRIPTION +'.doc' from tbl_France where QC_DESCRIPTION is not null
)

OPEN file_cursor

FETCH NEXT FROM file_cursor INTO @file_path

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @cmd = 'EXEC xp_cmdshell ''del ' + @file_path + ''''
EXEC(@cmd)

FETCH NEXT FROM file_cursor INTO @file_path
END

CLOSE file_cursor
DEALLOCATE file_cursor

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-28 : 06:26:37

you can't delete in cmd Files with space or blanks in filename. I think this the msg what i get.

output
======
Could Not Find C:\Data\Integration
Could Not Find C:\Windows\system32\Lettre

Last version:
DECLARE @image_files TABLE (file_path VARCHAR(MAX))
DECLARE @file_path VARCHAR(MAX), @cmd VARCHAR(MAX)

INSERT INTO @image_files (file_path)
EXEC xp_cmdshell 'C:\Data\*.doc /b /s /x'

DECLARE file_cursor CURSOR FOR
SELECT file_path FROM @image_files
WHERE file_path IN
(
select 'C:\Data\' + QC_DESCRIPTION +'.doc' from tbl_France where QC_DESCRIPTION is not null
)

OPEN file_cursor

FETCH NEXT FROM file_cursor INTO @file_path

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @cmd = 'EXEC xp_cmdshell ''del ' + @file_path + ''''
EXEC(@cmd)

FETCH NEXT FROM file_cursor INTO @file_path
END

CLOSE file_cursor
DEALLOCATE file_cursor

[/quote]
[/quote]
[/quote]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-28 : 07:27:39

So...did you wrap the fun in quotes?

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-28 : 10:19:18

Can someone tell me whats wrong with this code ? I try to delete with + '"' cmd but SQL does not accept it.

SET @cmd = 'EXEC xp_cmdshell ''del ''"' + @file_path + '"' + ''''

>Output
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'C:\Data\Integration Lettre 2 purchase100000_10000020130710012102601100000.doc'.

to delete with via cmd you need this Syntax with high comma '"' del "C:\Data\Integration Lettre 2 purchase100000_10000020130710012102601100000.doc"

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-28 : 10:54:51

Finaly i found it and it work's work fine.

SET @cmd = 'EXEC xp_cmdshell ''del ' + '"'+ @file_path + '"' + ''''

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-28 : 10:59:49

Can you confirm that this script works in your setting (you'll need a C:\temp directory:

[code]
xp_cmdshell 'echo one > "c:\temp\one on one.txt"';
go
xp_cmdshell 'dir "c:\temp\one on one.txt"';
go
declare @fn varchar(100) = 'c:\temp\one on one.txt';
declare @cmd varchar(100) = 'del ' + '"' + @fn + '"'
exec xp_cmdshell @cmd
[/code

zero1de
Posting Yak Master

105 Posts

Posted - 2014-11-28 : 11:23:38

Yes it does.You need only adjust either filename in file system or in table. I have adapted easily filename of the table with the path. And for delete you can use.
'EXEC xp_cmdshell ''del ' + '"'+ @file_path + '"' + '''' with double quota if you have spaces or blanks in your filename.

Thx for your support Gbritton.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources