Post reply

How i can delete specific physical files using SQL 2005 SP

  • March 11, 2009 at 10:15 pm

    #197998

    how I can delete specific files using Stored Procedures.

    There are Two servers CO1 and CO2.

    One (CO1) has Sql 2005 runing. Another (CO2) is the webserver and has image and video files stored.

    I want runing Stored procedure that time delete CO2 system files. Is it possible? I tried using the following code

    declare @ImagePath varchar(100)

    DECLARE @cmd varchar(1000)

    select @ImagePath= PhotoPath from UserPhotoAlbumInfo WHERE UserAlbumInfoId = @UserAlbumInfoId

    SET @cmd = 'del "\\192.183.1.19\e$\webprojects\My\Files\Images\UserPhotoAlbum\' + @ImagePath +'"'

    EXEC master..xp_cmdshell @cmd

    This procedure is not working as i need to provide the credentials for C02. Where would i need to give the credentials in the SP ?

    Also is it a good practice to delete the files physically when a delete operation is requested or would it be best if we run a scheduler remove the physical files?

    Thanks in advance

    Suresh

  • March 11, 2009 at 10:46 pm

    #958071

    The account that xp_cmdshell is running under will have to have permission to delete the file.

    xp_cmdshell runs under the SQL Server service account if it is executed by a sysadmin login, otherwise it runs under the credentials of the SQL Server proxy account.

  • March 12, 2009 at 5:59 am

    #958228

    To add to Michel ...

    doing a delete from \\192.183.1.19\e$ is not good idea.

    Instead i would create a new share and point it to the folder where you have your pic and vid files. Create a new windows account give it access to that folder and define it as proxy account with in SQL Server for deletion of the files :).

    Thanks.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • March 12, 2009 at 6:08 am

    #958240

    I wouldn't use SQL Server to do this. Just setup a windows scheduler task on the web server to kill the files on your schedule (if needed). And when you delete through the application, just delete it from the file system and call the sql code after successful deletion of the file to clear up the records

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply