September 26, 2002 at 10:15 am
I've got a script I'm working with that I'm trying to nail the syntax on. I've looked at a few different ways and they all looked right to me. However, they aren't doing what I want them to.
I've got a file directory, with files in it. I'm creating a one-column, temp table and then passing the results of a DIR /b list into that temp table:
i.e.
File_a.txt
File_b.txt
File_c.txt
File_d.txt
File_e.txt
File_f.txt
The cursor is there, but when I pass all my parameters to local variables, I can't get the xp_cmdshell syntax right. If I'm allowing the curs to pass one of these file names into a variable like '@DELETE", what's the syntax to pass that @DELETE variable into the cmdshell to get it to delete? As of now I've used these...
exec master..xp_cmdshell 'del E:\backups\@DELETE'
exec master..xp_cmdshell 'del E:\backups\' @DELETE
exec master..xp_cmdshell 'del E:\backups\(@DELETE)'
and none of them work. The statement runs without errors, it just doesn't delete anything. Does anyone know what the syntax would be?
September 26, 2002 at 10:20 am
select @cmd = 'del E:\backups\' + @DELETE
xp_cmdshell @cmd
Steve Jones
September 26, 2002 at 10:31 am
Might try this:
declare @delete varchar(100)
declare @cmd varchar(100)
set @delete = 'db_dbk.sql'
set @cmd = 'exec master..xp_cmdshell ''del E:\backups\' + @delete + ''''
print @cmd
exec (@cmd)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 26, 2002 at 12:19 pm
I have a lot of scripts that perform functions such as zipping or rar'ing files and then deleting the old files after verification.
The answer from Greg above would be the best as there are issues with passing filenames which have spaces in them which his answer will handle by enclosing the path and filename in quotation marks, though the other methos would work for the filenames you show. Another piece of advice I would give would be to use UNC paths and filenames for remote file operations rather than mapped drives if doing remote operations (this one got us once before) unless your specifics give reason not to.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply