DOS batch file not setting the value of count

Question

I have a batch job that runs every week to calculate money spent by employees in a week.

WriteToDBIndex is a C# program that checks other process like if finance have finished processing data (i.e. entered all employees spending data). If yes, WriteToDBIndex, writes a row in emptablestat table with JobStatusName & status if all data is entered.

I need to keep checking this until count for FINANCE & empstatus=1 is one. I have timeout set for 3600 seconds.

@ECHO OFF
set ERRORLEVEL=0
REM start "" "C:\WriteToDBIndex.exe"
:gobackloop
set ct=0
if %ERRORLEVEL% NEQ 0 (
Echo There has been an error. 
goto end
)
if %ERRORLEVEL% EQU 0 (
Echo inside errorlevel zero
for /F "usebackq" %%i in (`sqlcmd -E -S "MYDATA\MYDB" -h-1 -Q "SELECT COUNT(*) FROM [dbo].[emptablestat] where JobStatusName like '%FINANCE%' and empstatus=1"`) do set ct=%%i 

if %ct% EQU 1 (
echo starting 1
start "" "C:\CalculateMoneySpent.exe"
timeout /t 60
) 
if %ct% EQU 0 (
timeout /t 3600
goto :gobackloop
)
)
:end
echo END
REM cd ..

The value of variable ct is somehow always zero even after I have the count of one in emptablestat table. I am not sure why.

Thanks R

Answer 1

First, read what MC ND wrote as answer on IF statement in batch causing issues. Works when ran individually? as it explains why environment variable ct is always read with value 0 below for loop in the if %ERRORLEVEL% EQU 0 block.

Second, see the batch code below which I could not test at all, but hopefully works.

@echo off
rem Running WriteToDBIndex.exe in a parallel process.
start "Write to DB index" "C:\WriteToDBIndex.exe"
if errorlevel 1 (
   echo Error: Failed to start WriteToDBIndex.exe!
   goto end
)

:gobackloop
set ct=0
for /F "usebackq" %%i in (`sqlcmd -E -S "MYDATA\MYDB" -h-1 -Q "SELECT COUNT(*) FROM [dbo].[emptablestat] where JobStatusName like '%FINANCE%' and empstatus=1"`) do set ct=%%i

if %ct% EQU 1 (
   rem Running CalculateMoneySpent in this process.
   "C:\CalculateMoneySpent.exe"
) else if %ct% EQU 0 (
   timeout.exe /t 3600
   goto gobackloop
)

:end
cd ..

I'm not sure why you use command start to run WriteToDBIndex.exe in a separate process which is running parallel to this batch process.

A reason could be that WriteToDBIndex.exe runs very long and you want to finish this batch process already before WriteToDBIndex.exe terminated.

Or WriteToDBIndex.exe is a Windows (GUI) application and this is the reason for using command start although I don't know why not using in this case additionally parameter /wait to halt execution of the batch file until WriteToDBIndex.exe terminated.

But I suppose you have used command start because simply not knowing the different execution behavior of calling a console application with or without start. Therefore I suppose further that CalculateMoneySpent.exe is a console application with short execution time and removed command start.

See my answer on How to call a batch file in the parent folder of current batch file? where I explained the 4 possibilities to start or call another batch file / console / GUI application from within a batch file.