Do stored procedures have the ability to delete a file from the OS?

Question

Out of curiosity, does a stored procedures have the ability to delete a file from the OS?

If not I will have to make a windows Batch file that deletes the file and then runs the stored procedure using OSQL.

Answer 1

Technically, with the correct permissions, you could execute xp_cmdshell to issue commands to the OS (or call a batch file, whatever), but it's probably not a good idea. If you do use that method, be very strict about permissions.

Edited for clarity.

Answer 2

Try this

Option 1 delete a file using xp_cmdshell

xp_cmdshell 'del y:\file.dat'

Option 2 delete a file using OLE Automation

DECLARE @ResultOP int
DECLARE @OLE_Obj  int

EXEC @ResultOP = sp_OACreate 'Scripting.FileSystemObject', @OLE_Obj OUTPUT
EXEC @ResultOP = sp_OAMethod @OLE_Obj, 'DeleteFile', NULL, 'y:\file.dat'
EXEC @ResultOP = sp_OADestroy @OLE_Obj

Answer 3

You could also use a CLR stored procedure for this. That's one of the main reasons for the existence of managed stored procedures, to interact with the OS in a safe manner.