如何通过 MS SQL Server 删除目录中的文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43269779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete files on the directory via MS SQL Server
提问by Nisal Malinda Livera
I am trying to delete a file from a directory inside windows using the following query,
我正在尝试使用以下查询从 Windows 内的目录中删除文件，
exec xp_cmdshell 'del "C:\root\sfd_devtracker\'+@deletefile + '"';
When i execute this command it gives the following error,
当我执行此命令时，它会出现以下错误，
Incorrect syntax near '+'.
In @deletefilevariable i have the filename which i have to delete. What have i done wrong here?
在@deletefile变量中，我有我必须删除的文件名。我在这里做错了什么？
回答by Serge
xp_cmdshellrequires that a literal string be passed as parameter. You cannot construct a value on the fly.
xp_cmdshell要求将文字字符串作为参数传递。您不能即时构建值。
Try this:
尝试这个：
DECLARE @cmd NVARCHAR(MAX) =
'xp_cmdshell ''del "C:\root\sfd_devtracker\' + @deletefile + '"''';
EXEC (@cmd)
Consider that xp_cmdshellmust be enabled, for instance in this way.
考虑xp_cmdshell必须启用，例如以这种方式。
回答by Lucenio Marques
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
学分：https: //www.patrickkeisler.com/2012/11/how-to-use-xpdeletefile-to-purge-old.html
