Do stored procedures have the ability to delete a file from the OS?
Out of curiosity, do stored procedures store the ability to delete a file from the OS?
If not, I will have to create a Windows batch file that will delete the file and then run the stored procedure using OSQL.
+2
djangofan
source
to share
3 answers
Technically, with the correct permissions, you can run the xp_cmdshell command to issue OS commands (or invoke a batch file, whatever), but that's probably not a good idea. If you use this method, be very strict about permissions.
Edited for clarity.
+6
atfergs
source
to share
try it
Option 1 delete the file using xp_cmdshell
xp_cmdshell 'del y:\file.dat'
Option 2 delete the 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
+3
RRUZ
source
to share
You can also use a CLR stored procedure for this. This is one of the main reasons for the existence of managed stored procedures in order to communicate securely with the OS.
+1
Eric Z Beard
source
to share