Delete Files In SQL Server

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

Tags: