CODEWITHSUNDEEP
sql-serversql-server-2005stored-procedures
djangofan
djangofan

Reputation: 29669

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

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.

Upvotes: 2

Views: 6612

Answers (3)

Eric Z Beard
Eric Z Beard

Reputation: 38406

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.

Upvotes: 1

RRUZ
RRUZ

Reputation: 136421

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

Upvotes: 3

atfergs
atfergs

Reputation: 1684

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.

Upvotes: 6

Related Questions