T-SQL script - Delete files using file name pattern Ole Automation Procedures

Use the Ole Automation Procedures option to specify whether OLE Automation objects can be instantiated within Transact-SQL batches. This option can also be configured using the Policy-Based Management or the sp_configure stored procedure. Ole Automation Procedures is an alternate approach for xp_cmdshell to handle file system.

The following T-SQL script shows that how to delete file without xp_cmdshell 

IF OBJECT_ID('dbo.sp_Clean_Files') IS NOT NULL

DROP PROC dbo.sp_Clean_Files

GO

CREATE PROCEDURE dbo.sp_Clean_Files

      @Temp_File_Path varchar(300) = 'C:\temp', 

-- Temp folder path as input parameter

      @Temp_File_Prefix varchar(100) = '0101', 

-- Temp file prefix as input parameter

      @Temp_File_Extension varchar(10) = 'TMP' 

-- Temp file extension as input parameter

AS

BEGIN

      SET NOCOUNT ON;

     

      EXEC sp_configure 'show advanced options', 1;

      RECONFIGURE;

      EXEC sp_configure 'Ole Automation Procedures', 1;

      RECONFIGURE;

     

            DECLARE @ResultOP int

            DECLARE @OLE_Obj  int

            DECLARE @TempFullPath varchar(300)

            SET @TempFullPath = @Temp_File_Path +'\'+@Temp_File_Prefix+'*.'+@Temp_File_Extension -- define temp folder full path

            EXEC @ResultOP = sp_OACreate 'Scripting.FileSystemObject', @OLE_Obj OUTPUT

            EXEC @ResultOP = sp_OAMethod @OLE_Obj, 'DeleteFile', NULL, @TempFullPath

            EXEC @ResultOP = sp_OADestroy @OLE_Obj

      EXEC sp_configure 'Ole Automation Procedures', 0;

      RECONFIGURE;     

      EXEC sp_configure 'show advanced options', 0;

      RECONFIGURE;

END

GO

Cheers!

Uma