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.

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.

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.

 

Regards

Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook