Batchfile Calling SQLCMD To Call A SQL Server Agent Job

Hi All,



You have to forgive me but I am relatively new to this. I created a batch file which would call a SQL Server Agent Job. The said job calls SSIS packages. The last step for the job was to update a table which contains the next run date for the job. The batch file, after calling the job, would then query the table where the next run date is stored and place it in a log file. However, it seems that the log file gets updated first. The user who clicked on my batch file assumes that the job is finished and would proceed to run another program which scans all the records affected by the job. Since the job is not yet finished, the few records that were affected are the ones that are only scanned leaving the other records that are yet to be transferred.



Does calling SQLCMD from a batch file spawn a new thread for the job?



Why does the date get updated even though the job is not finished yet?



I have attached the bat file I created. Below that also is the control flow

@echo off
echo.
echo Executing job PRD_ICBS...
echo.
echo Please wait...
SQLCMD -Q "EXEC msdb.dbo.sp_start_job @job_name='TRANSFER_DATA'"
echo.
echo Creating folder %date:~-4,4%-%date:~-10,2%-%date:~-7,2%...
md %date:~-4,4%-%date:~-10,2%-%date:~-7,2%
cd %date:~-4,4%-%date:~-10,2%-%date:~-7,2%
date/t >> TRANSFER_DATA.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD >> PRD_ICBS.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA >> PRD_ICBS.txt
echo PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS >> PRD_ICBS.txt
SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_TD.txt"

SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_CASA.txt"

SQLCMD -Q "SELECT next_rundate FROM db.NEXT_RUNDATE_PARAM WHERE NAME = 'PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS'" -o "PRD_Insert_HMTRANSACTIONDATADETAILS_From_ICBS_LOANS.txt"


----------------------------- ----------------------------------------
| | | |
| Transfer Data | ===> | Update NEXT RUN DATE | |
| | | |
----------------------------- ----------------------------------------


Thanks in advance