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.