Clever Geek Handbook

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


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


source


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


source


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


source






More articles:


All Articles