?
Solved

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

Posted on 2003-02-25
6
Medium Priority
?
673 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: 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!

 

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

765 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