Retry a sql query in case of failure in batch

Question

This script processes all the sql files in a given folder and outputs the result to a csv. Do you have any ideas how could I adapt it to retry the sql file in case of error or failure?

@ECHO OFF

SET SQLCMD="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
SET PATH="C:\Users\username\Desktop\Scripts\Reports\sql"
SET SERVER="localhost"
SET DB="database"
SET LOGIN="username"
SET PASSWORD="password"
SET OUTPUT="C:\Users\username\Desktop\Scripts\Reports\output_%date%-%time:~0,2%-%time:~3,2%-%time:~6,2%.csv"

CD %PATH%

ECHO %date% %time% > %OUTPUT%

for %%f in (*.sql) do (
%SQLCMD% -S %SERVER% -d %DB% -E -i  %%~f >> %OUTPUT% -W -w 1024 -s";")

Thank you!

Answer 1

You cant test the ERRORLEVEL environment variable. typically a zero ERRORLEVEL value means success. also you need to enable delayed expansion to check it inside a block.

something like this may help,

SetLocal EnableDelayedExpansion
for %%f in (*.sql) do (
  Set /a success=1
  for /L %%w in (1,1,5) do ( rem retry five times
    if !success! NEQ 0 (
       %SQLCMD% -S %SERVER% -d %DB% -E -i  %%~f >> %OUTPUT% -W -w 1024 -s";"
       if !ERRORLEVEL! EQU 0 set /a success=0
    )
  )
  if !success! NEQ 0 (
     rem sql failed, log or advise
  )
)
EndLocal

another important point, be careful with the PATH environment variable. you should better use another name as SQL_PATH or MY_PATH.

or you can use PUSHD & POPD to change your working dir

@echo off

SET ...

rem save current dir and jump to...
pushd "C:\Users\username\Desktop\Scripts\Reports\sql" 

for %%f in (*.sql) do (
   ...
   ...
)

rem restore dir saved by pushd
popd