T-SQL script to delete files

Many a times need arises to access/modify windows folder structures from SQL server. Though SQL Server is not meant for this purpose, however here is a T-SQL script to delete files from SQL Server.

One way is to delete the file by running the DEL DOS command via xp_cmdshell extended procedure.

-- enable xp_cmdshell
sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO
sp_configure 'xp_cmdshell'
GO
-- run del dos command to delete a file/folder
xp_cmdshell 'DEL E:\Deleted\del01.txt'

The above script first checks that xp_cmdshell is enabled and then deletes the file by executing DEL DOS command.

Another way is to use Ole Automation procedures as shown below.

-- using OLE Automation Procedures 
exec sp_configure
GO
-- enable Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @Filehandle int

-- create a file system object
EXEC sp_OACreate 'Scripting.FileSystemObject', @Filehandle OUTPUT
-- delete file 
EXEC sp_OAMethod @Filehandle, 'DeleteFile', NULL, 'E:\Deleted\del01.txt'
-- memory cleanup
EXEC sp_OADestroy @Filehandle

The above query first checks that Ole Automation procedures are enabled or not using sp_configure procedure, if not it enables it. It then creates a FileSystemObject and then calls sp_OAMethod with the @filehandle, DeleteFile and file name to delete a particular file.  The sp_OADestroy is to clean up the memory used by the unused objects.

 
Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook