Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2386
  • Last Modified:

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
0
erin027
Asked:
erin027
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

DECLARE @cmd varchar(1000)
set @cmd = " del ""C:\test\product_images\" + @CategoryImage + """ "
EXEC master..xp_cmdShell @cmd
0
 
erin027Author Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now