Category: SQL Server
How to Delete Files In SQL Server:
You can do this using the extended stored procedure. The problem is you need to configure the database to allow this to run. You need to use sql server “service area” configuration. You also need sys admin rights to run this.
EXEC master..xp_cmdshell 'del C:file.txt'
To delete files without requiring this access, use the built-in SQL Server Automation and the FileSystemObject. This also needs to be configured in the database to allow this to run. You need to use sql server “service area” configuration.
DECLARE @hr int DECLARE @fileSystem int EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fileSystem OUT EXEC @hr = sp_OAMethod @fileSystem, 'DeleteFile', NULL, 'C:pathtofile.txt' EXEC @hr = sp_OADestroy @fileSystem