sqlsql-serverxp-cmdshell

How to delete files on the directory via MS SQL Server

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?

Solution

  • 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.