How to delete files on the directory via MS SQL Server

Question

I am trying to delete a file from a directory inside windows using the following query,

exec xp_cmdshell 'del "C:\root\sfd_devtracker\'+@deletefile + '"';

When i execute this command it gives the following error,

Incorrect syntax near '+'.

In @deletefile variable i have the filename which i have to delete. What have i done wrong here?

score 23 · Accepted Answer · edited Jan 12 '19 at 10:57

23

xp_cmdshell requires that a literal string be passed as parameter. You cannot construct a value on the fly.

Try this:

DECLARE @cmd NVARCHAR(MAX) = 
'xp_cmdshell ''del "C:\root\sfd_devtracker\' + @deletefile + '"''';
EXEC (@cmd)

Consider that xp_cmdshell must be enabled, for instance in this way.

edited Jan 12 '19 at 10:57

bluish

26,356
27
122
180

answered Apr 07 '17 at 04:46

Serge

3,986
2
17
37

the query didn't work. it says, `Incorrect syntax near 'C:\root\sfd_devtracker\'.` – Nisal Malinda Livera Apr 07 '17 at 05:00
Works fine for me as long as I declare the variable : `declare @deletefile varchar(20);` – Nick.Mc Apr 07 '17 at 05:03
@NisalMalindaLivera, I revised the answer and moved the double quote. Should work now. – Serge Apr 07 '17 at 05:05
@Nick.McDermaid How on earth does that work fine for you? It's not valid syntax... It should be `'xp_cmdshell ''del "C:\root\sfd_devtracker\' + @deletefile + '"'''` – ZLK Apr 07 '17 at 05:06
Works fine as in 'does not throw that error'. It certainly doesn't do anything. – Nick.Mc Apr 07 '17 at 05:11
Thank you @Serge and ZLK after revising the double quotes i managed to execute the query successfully. – Nisal Malinda Livera Apr 07 '17 at 05:21

score 4 · Answer 2 · answered Apr 30 '20 at 20:58

4

declare @typeFile int = 0; -- for backup files or 1 for report files.
declare @folderPath varchar(max) = N'C:\temp'; --The folder to delete files.
declare @fileExtension varchar(100) = N'bak'; --File extension.
declare @cutOffDate datetime = DATEADD(hour , -12, getdate()); --The cut off date for what files need to be deleted.
declare @subFolder int = 0; --0 to ignore subFolders, 1 to delete files in subFolders.


EXECUTE master.dbo.xp_delete_file @typeFile, @folderPath, @fileExtension, @cutOffDate, @subFolder;

Credits: https://www.patrickkeisler.com/2012/11/how-to-use-xpdeletefile-to-purge-old.html

answered Apr 30 '20 at 20:58

Lucenio Marques

41
2

Although you've given a possible solution here, you haven't actually answered the question. I think this answer could be improved by answering what is wrong with the OP's code, and why a different solution is necessary, better, etc. – guysherman Apr 30 '20 at 22:26
This is only useful for deleting database and log backup files, according the the blog post referenced. "Don’t let the stored procedure file name, xp_delete_file, fool you. Microsoft has some internal code that only allows for the deletion of database and transaction log backup files. The stored procedure cannot be used to delete any other type of file." – rocky May 18 '22 at 18:22

How to delete files on the directory via MS SQL Server

2 Answers2

Linked