October 22, 2008 at 12:05 pm
I have this DOS command that runs successfully
SETLOCAL ENABLEDELAYEDEXPANSION
SET FOLDER=E:\SQL Backup\Data
FOR /F "Tokens=*" %%a in ('dir /a-d /b "%FOLDER%\*.bak"') DO (
Set Filename=%%~na
Set Filename=!Filename:~0,-8!
ECHO Renaming "%FOLDER%\%%a" to "%FOLDER%\!Filename!%%~xa"
REN "%FOLDER%\%%a" "!Filename!%%~xa"
)
I want to put this in SQL using SET but when I place quotes around it
SET @xpcommand1 = 'SETLOCAL ENABLEDELAYEDEXPANSION
SET FOLDER=E:\SQL Backup\Data
FOR /F "Tokens=*" %%a in ('dir /a-d /b "%FOLDER%\*.bak"') DO (
Set Filename=%%~na
Set Filename=!Filename:~0,-8!
ECHO Renaming "%FOLDER%\%%a" to "%FOLDER%\!Filename!%%~xa"
REN "%FOLDER%\%%a" "!Filename!%%~xa"
)'
I get an error about the 'dir /a-d /b "%FOLDER%' syntax...the rest of the code is in red but this section is in black text...I have to double-quote somewhere to include all the text into red but the combinations I have done do not work.
Do I need to do a SET QUOTE IDENTIFIER before my DECLARE of the variable?
October 22, 2008 at 12:15 pm
This should work. You have to replace any tick marks with double tick marks. One thing I usually do is use the print statement to help debug string operations.
Declare @xpcommand1 varchar(1000)
SET @xpcommand1 = 'SETLOCAL ENABLEDELAYEDEXPANSION
SET FOLDER=E:\SQL Backup\Data
FOR /F "Tokens=*" %%a in (''dir /a-d /b "%FOLDER%\*.bak"'') DO (
Set Filename=%%~na
Set Filename=!Filename:~0,-8!
ECHO Renaming "%FOLDER%\%%a" to "%FOLDER%\!Filename!%%~xa"
REN "%FOLDER%\%%a" "!Filename!%%~xa"
)'
PRINT @xpcommand1
October 22, 2008 at 12:23 pm
Ugh...Ken thanks that formats it correctly but now the script will not run. Altering the quotes causes the file renaming to not work.
If I take this text and run in DOS it returns a syntax error.
October 22, 2008 at 12:51 pm
You can always create a batch file and call the batch file from xp_cmdshell.
October 22, 2008 at 1:37 pm
Thanks...I was going that route my only concern is that xp_cmdshell will only error if it cannot fire off the .bat file not properly executing the command within the .bat file
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply