I have a scheduled task with a .bat file that downloads some files from a web server every day by the morning then process the data and UPDATES a database. Then it triggers another .bat file to SELECT data and EXPORT to a .xls file.
The second .bat file is like this:
set a=%date:/=-% del /q F:file_pathfile1_%a%.xls del /q F:file_pathfile2_%a%.xls echo %time%_%date% cd /D D:oracleproduct10.2.0db_1BIN sqlplus usrname/psswd@ORCL @F:select_pathselect1.sql timeout /t 30 /nobreak > nul ren F:file_pathfile1.xls file1_%a%.xls sqlplus usrname/psswd@ORCL @F:select_pathselect2.sql timeout /t 30 /nobreak > nul ren F:file_pathfile2.xls file2_%a%.xls cd /D F:KMB-SPTIScriptsscript_select ::Command to send file1 and file2 via e-mail.
But when I arrive at the office and check the progress, only the first .xls is done. So I have to run the second .bat manually and it runs perfectly.
What could be causing this?
- I put the timeout between the two SELECTs because, in the past, the code was stopping after the INSERT and didn’t trigger the second .bat . My colleague said it could be execution exception. Puting a timeout would give time to end the INSERT properly.
- Before, it used to make both SELECTs and then rename both files. Doing so, sometimes it worked, sometimes not, then I tried to change the order: select1, rename1, select2, rename2.
- As we download files everyday, we concatenate the data on a single file called DT-date. The first code goes like this:
rem The data is downloaded and the files are organized in their files if exist F:pathDT-date ( Data_consolidation.exe timeout /t 300 /nobreak > nul F:pathsecond_bat.bat ) else (exit)
- As @William Robertson said, I tried
echo exitright after the first SELECT, but again, it only extracted the first file and not the second one.
As @WilliamRobertson suggested, writing
echo exit | before the sqlplus commands solved the problem.