asked on

trigger should delete file... (and does not!)

Hi all!

I want to delete a file, if the according record in my database is deleted (no problems with adding files).
I set up a delete-trigger, which calls the following stored proc (using xp_cmdshell):

CREATE PROCEDURE [dbo].[TP_DELIMAGE]
@SerID AS BIGINT,
@ImgID AS BIGINT

AS

/* Build path of image to be deleted */
DECLARE @sPath sysname

SET @sPath = [..build the path out of other tables.. no problem with that!]
/* build command string to be processed */
DECLARE @sCmd sysname

SET @sCmd= ' DEL C:\' + @sPath + '\' + CAST(@ImgID AS NVARCHAR(256))

/* execute command string */
EXEC master..xp_cmdshell @sCmd, no_output
GO

This all works fine, if I am connected as sa or dbo, if I login as user then I encounter the error:

xpsql.cpp: Error 997 from GetProxyAccount on line 499

I found this has to do with an access problem of xp_cmdshell.

Question 1:
I'm just interested to delete a specific file in a defined area when a record is deleted. Are there any other solutions to do that besides using xp_cmdshell?

Question 2:
How do I have to set the rights on xp_cmdshell to allow users to use that SP?

Any hint is appreciated...

Duc

ASKER CERTIFIED SOLUTION

adathelad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nigelrivett

It is best to keep triggers as short as possible as they hold a transaction an d will block the table.

Do you want the delete to fail or hang if the file delete can't be carried out?

May be better to put the file delete request into a table then have a scheduled task which performs the actual delete - you can run this under sa and get round the permission problems.

hakyemez

Open Ent.Mng. and go to Security>Server Roles
and add your username to System Administrator or disk administrator roles...

Duc_de_Belfort

ASKER

Hi all

thnx a lot for your comments. With adathelad's hint I found the following (working) solution:

CREATE PROCEDURE [dbo].[TP_DELFILE]
@SerID AS BIGINT,
@ImgID AS BIGINT

/* deletes the file according to a record in R_IMAGES
residing in C:\....\@ImgID */
AS
/* Build path of image to be deleted */
DECLARE @sPath sysname
DECLARE @fso int
DECLARE @OLEResult int

SET @sPath = [..build path...]
SET @sPath= 'C:\' + @sPath + '\' + CAST(@ImgID AS NVARCHAR(256))

-- Create Scripting.FilesystemObject
EXEC @OLEResult = sp_OACreate 'Scripting.FilesystemObject', @fso OUTPUT
IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)

EXECUTE @OLEResult = sp_OAMethod @fso, 'DeleteFile', Null, @sPath
as the whole folder is deleted by parent table trigger
IF @OLEResult <> 0 RAISERROR ('DeleteFile %0X', 14, 1, @OLEResult)

EXEC @OLEResult = master.dbo.sp_OADestroy @fso
IF @OLEResult <> 0 RAISERROR ('DestroyObject %0X', 14, 1, @OLEResult)
GO

Nevertheless, I will check out nigelrivetts interesting idea in a later phase, according to the goal "make it work, make it save, make it fast....", thanks anyway (how do I by the way set up a scheduled task? Sorry, may be a newbie-question... ;-)).

Thanks to hakyemez for the hint, but I don't want to grant normal users admin rights. However I found a solution for that. You may declare a new account which you enter in EM|SQL Server Agent|Properties|Job System|Non-Sys-Admin job step proxy account. This user does not need to have admin rights, I granted this user even restricted user-rights and nevertheless the procedure ran whithout error.

That was all, thanks again for your help.
Have a nice day,

Duc

Duc_de_Belfort

ASKER

Would have liked to get directly some example code...
;-) would have been luxurious and saved time.

Duc

Duc_de_Belfort

ASKER

Found another info for using xp_cmdshell by non sysAdmins:
http://www.sqlmag.com/forums/messageview.cfm?catid=13&threadid=8458