SQL Agent: Use a batch file to run RoboCopy

\ by

It’s really hard to run RoboCopy in a SQL Agent command step. We want to use CmdExec so we can execute using a DOS proxy. But RoboCopy has a lot of weird exit values.

Easily handle the exit codes by calling a batch file. I’ve hard coded our options to make it easy and standard. 

@ECHO ON

IF {%3}=={} (
  @ECHO USAGE
  @ECHO RoboCopyFiles SourcePath DestinationPath Files
  @ECHO.
  GOTO FINISHED
) ELSE (

IF NOT {%4}=={} (
  (robocopy %1 %2 %3 /COPY:DAT /NP /R:0 /A+:A) 
) ELSE (
  (robocopy %1 %2 %3 /COPY:DAT /NP /R:0 /A+:A  /L) 
)

)
:FINISHED
IF %ERRORLEVEL% GEQ 8 exit /B %ERRORLEVEL%
exit /B 0
Posted in: Coding, DOS

PowerShell: Get the first files in each folder for each day

\ by 
$SetFileNames = @()
$TESTFiles | 
    Sort-Object Directory, LastWriteTime, Name | 
        Group-Object -Property {Get-Date $_.LastWriteTime -Format d } |
            ForEach-Object {
                $_.Group |
                    ForEach-Object -Begin {
                        $counter = 0
                        $FirstFile = $_ 
                        # Grab the files for each folder which are the first file of each day.
                        $SetFileNames += $FirstFile.Group[0]
                    } -Process {
                        $counter++;
                        # Do work here
                    }
            }
$SetFileNames
Posted in: DOS, PowerShell

CMD: Parse DATE to get a string for searching files

\ by

Sometimes we want to look for files with dates like yyyyMMdd in the name. So for given this code we can run in DOS CMD:

for /f "tokens=1-4 delims=/-. " %i in ('date /t') do @ECHO %i %j %k %l

Thu 02 21 2019

We can make our batch file to search for files with today’s date in name, using multiple %%s.

@ECHO OFF
 
rem First list everything sorted by date.
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do DIR  /OD "\\FileServer\?M*%%l%%j*.txt"

@ECHO.
@ECHO **SEARCHING for six ?M_ and one ?M_ files ...
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do @ECHO ...for date ?M*%%l%%j%%k*.txt && @ECHO. && DIR /OD "\\FileServer\?M*%%l%%j%%k*.txt"
Posted in: Coding, DOS, Files

xp_cmdshell error: ‘C:\Program’ is not recognized as an internal or external command,

\ by

Apparently in my 64-bit environment I can’t execute the following command.

DECLARE @vcCmd VARCHAR(8000)
SELECT @vcCmd = '"C:\Program Files\Winzip\WZZIP.exe" -es -a "E:\DATA\test.zip" "E:\DATA\test.dat"'
EXEC   master..xp_cmdshell @vcCmd = @vcCmd
/*
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
*/

The solution is I can execute other DOS commands, so let’s concatenate them.

DECLARE @vcCmd VARCHAR(8000)
SELECT @vcCmd = 'C: && CD "C:\Program Files\Winzip\" && WZZIP.exe -es -a "E:\DATA\test.zip" "E:\DATA\test1.dat"'
EXEC   master..xp_cmdshell @vcCmd = @vcCmd
Posted in: DOS, SQL

COPY multiple files to single file 0x1A problem

\ by

Char Dec Oct Hex
(sub) 26 0032 0x1a

For some reason COPY, yes the DOS COPY command, adds 0x1a to the end of a multiple file COPY command. You can see the SUB at the end of a file in NOTEPAD 2.

One way to get a “header row” or a list of column names from a table for a bcp out is to append a header file and the data file.

The only way I’ve found is to use COPY. If we simply tell COPY the files are all BINARY, it won’t add the 0x1a to the end.

COPY /B File1+File2 File2
Posted in: DOS