?
Solved

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

Posted on 2003-02-25
6
Medium Priority
?
675 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:Duc_de_Belfort
6 Comments
 
LVL 23

Accepted Solution

by:
adathelad earned 240 total points
ID: 8015785
Hi,

1) Yes, there are other ways, although a bit more complicated that your current method. 1 possible alternative is using sp_OACreate to create an instance of Scripting.FileSystemObject commonly used in ASP/VB. You can then call the DeleteFile method (I think thats the method), passing to it the file name to delete. Then use sp_OADestroy to destroy your reference to the object.

2) xp_cmdshell can only be run by users that are members of sysadmin. You would need to make relevant users members of sysadmin.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8016383
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.
0
 
LVL 3

Expert Comment

by:hakyemez
ID: 8017891
Open Ent.Mng. and go to Security>Server Roles
and add your username to System Administrator or disk administrator roles...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Duc_de_Belfort
ID: 8023708
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


0
 

Author Comment

by:Duc_de_Belfort
ID: 8023720
Would have liked to get directly some example code...
;-) would have been luxurious and saved time.

Duc
0
 

Author Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question