How to delete files on the directory via MS SQL Server

sql sql-server xp-cmdshell
Nisal Malinda Livera picture Nisal Malinda Livera · Apr 7, 2017 · Viewed 38.3k times · Source

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?

Answer

Serge picture Serge · Apr 7, 2017

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.

Related questions

SQL xp_cmdshell copy files between servers

I am trying to move all .zip in a specific folder to another folder. the source folder is located on another server, currently i am using EXECUTE xp_cmdshell 'copy \\server1\e$\ETL\*.zip \\server2\e$\ETL\' GO Which …

Read More
How do I check if a directory exists using SQL Server?

I know this command will create a directory: EXEC master.sys.xp_create_subdir 'C:\testing\' But how do I check whether 'C:\testing\' exists? IF EXISTS(...

Read More
Create zip file from SQL Server

Here is code which I use to create a .rar file with a password DECLARE @source VARCHAR(1000), @destination VARCHAR(1000), @Command VARCHAR(1000) SET @source = 'E:\Temp\testRar.txt' SET @destination = 'E:\Temp\testRar.rar' SET @Command = '"C:\Program Files\WinRAR\…

Read More