How can I delete files from a location where the file already exist in database?

Question

How can I delete files which already exists in a database table as Filename. Example On Drive C:\Data there are 100 Word documents and 70 of these documents will be found in the database DMS.Filename. If directory.filename=table.filename then the File should be deleted. In this case we have to delete 70 Word documents. The procedure should run as daily task an check new files against the database. How can I check and delete the files ?

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

Could Not Find C:\Data\Integration

Could Not Find C:\Windows\system32\Lettre

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

** 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, Nov 28 '14 at 15:28

score 1 · Accepted Answer · edited May 23 '17 at 12:22

Haven't tested this code, but it should gives you a start: xp_cmdshell allows you to execute shell command from SQL Server:

You first need to enable it (credit pero):

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Then use a cursor to go through your table and delete files:

DECLARE @FileName varchar(200)
DECLARE @Command  varchar(300)

DECLARE FileName_Cursor CURSOR FOR
    SELECT [FileName] FROM MyTable

OPEN FileName_Cursor

FETCH NEXT FROM FileName_Cursor INTO @FileName

WHILE @@FETCH_STATUS <> 0
BEGIN
    SET @Command = 'del "' + @FileName + '"'
    EXEC xp_cmdshell @Command
    FETCH NEXT FROM FileName_Cursor INTO @FileName
END

CLOSE FileName_Cursor
DEALLOCATE FileName_Cursor

Note that there are security risks with this approach. It does not handle escape characters, or double quotes in the FileName. You will face problems like shellshock. it's best to use SSIS to read your table and delete files, or do it through application code.

How can I delete files from a location where the file already exist in database?

1 Answers1