Delete files using Ole Automation Procedures in SQL Server by serverku

To delete the files using Ole Automation Procedures in SQL Server we need to first enable 'Ole Automation Procedures' using sp_configure as following,

exec sp_configure 'Ole Automation Procedures', 1
go
reconfigure
go

After enabling it, we can delete the files with Ole Automation Procedures which using FSO (File System Object) from SQL Server and we need to pass method name 'DeleteFile' in the tsql script. You can delete all the files or specific files as well.

DECLARE @Result int
DECLARE @FSO_Token int
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, 'D:\TestFolder\*.txt'
EXEC @Result = sp_OADestroy @FSO_Token