asked on

How delete file by using variable with xp_cmdshell?

Hello,
I figured it out how to delete actual file using xp_cmdshell.
And here is the new problem.
How do I use variable?

This is the my coding and it doesn't work:

Declare @categoryImage nvarchar(30)
set @categoryImage = 'b_past.gif'

sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'xp_cmdshell', 1
go
reconfigure
go
EXEC master..xp_cmdShell " del C:\\test\product_images\@CategoryImage "
go
sp_configure 'xp_cmdshell', 0
go
reconfigure
go
sp_configure 'show advanced options', 0
go
reconfigure
go

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

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

Start Free Trial

erin027

ASKER

angellll,
Thank you.
It worked well.

I have another question though.
Can I use it in Store Procedure?
This is my coding:

--Create Store Proc DeleteCategory
Create Proc DeleteCategory (
@CategoryID int
, @CategoryImage nvarchar(30)
)
AS
SET NOCOUNT ON

--Delete the file from the server
If @CategoryImage <> Null
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'xp_cmdshell', 1
go
reconfigure
go

DECLARE @cmd varchar(1000)
set @cmd = ' del C:\test\product_images\' + @categoryImage
EXEC master..xp_cmdShell @cmd
go

sp_configure 'xp_cmdshell', 0
go
reconfigure
go
sp_configure 'show advanced options', 0
go
reconfigure
go

End

Delete Category where CategoryID = @CategoryID
Go

Guy Hengel [angelIII / a3]

yes, but without the GO statements.
you should NOT change the sp_configure for each individual file operation, ie you CANNOT make that part of the procedure without making the entire script dynamic sql.

note:
when the user has enough permissions to change the config in that way, that user (means owner of the procedure) should also be allowable to use xp_cmdshell...