I'm running SQL Server 2005 (Express) on Windows Server 2003 SP2. I'm using a scheduled task to launch a batch program that runs for several hours, during which many child processes are launched.
Almost all these child processes are running synchronously, so the root batch program waits for each child program to complete.
As one of the last actions in the root batch program, several child processes are called asynchronously using start
. This allows programs that depend on the work done by this scheduled task to start working on the updated data. The root batch program finishes by sending an email.
scheduled.bat
This is the batch program which is scheduled in Windows Scheduled Tasks.
cd backend
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined timestamp set timestamp=%%x
taskkill /F /FI "USERNAME eq admincm" /FI "WINDOWTITLE eq AtlasSync*"> Sync.Lte.%timestamp:~0,14%.log
title AtlasSync-%timestamp:~0,14%
sqlcmd -t 600 -d ATLAS -i Sync.Lte.build.sql -o Sync.Lte.bat
Sync.Lte.bat>> Sync.Lte.%timestamp:~0,14%.log
Sync.Lte.Bat (truncated)
The root batch program which calls sqlcmd
, putty
and mail
several times. It connects to a remote host, runs a few scripts there, then collects all the log files. It FTPs the data back to the Windows server, where it gets imported into the "ATLAS" database. As soon as the data has been imported, dependencies are called via start
.
D:
cd D:\PATH\Sync\backend
echo #!/bin/sh> DirtyNodes.Lte.sh
echo cd /home/cm/PATH/SYNC/>> DirtyNodes.Lte.sh
...
echo open 12.34.56.78> DirtyNodes.Lte.ftp
echo cm>> DirtyNodes.Lte.ftp
...
ftp -v -s:DirtyNodes.Lte.ftp
echo echo `date` -- cd /home/cm/PATH/SYNC/> DirtyNodes.Lte.putty
echo cd /home/cm/PATH/SYNC/>> DirtyNodes.Lte.putty
echo echo `date` -- chmod 755 DirtyNodes.Lte.sh>> DirtyNodes.Lte.putty
...
D:\PATH\TOOLS\putty -ssh -load AtlasSync -l cm -pw XXXXX -P 22 -m DirtyNodes.Lte.putty 12.34.56.78
echo #!/bin/sh> Refresh.Lte.build
echo MOS=/home/cm/PATH/SYNC/Refresh.Lte.mos>> Refresh.Lte.build
echo rm -f $MOS>> Refresh.Lte.build
...
echo #!/bin/sh> Refresh.Lte.sh
echo cd /home/cm/PATH/SYNC/>> Refresh.Lte.sh
echo echo `date` -- Running refresh amosbatch>> Refresh.Lte.sh
...
sqlcmd -t 600 -d CM -h -1 -W -i IpDatabase.Lte.build.sql -o ipdatabase
echo open 12.34.56.78> Refresh.Lte.ftp
echo cm>> Refresh.Lte.ftp
...
ftp -v -s:Refresh.Lte.ftp
echo echo `date` -- cd /home/cm/PATH/SYNC/> Refresh.Lte.putty
echo cd /home/cm/PATH/SYNC/>> Refresh.Lte.putty
echo echo `date` -- chmod 755 Refresh.Lte.build>> Refresh.Lte.putty
...
D:\PATH\TOOLS\putty -ssh -load AtlasSync -l cm -pw XXXXX -P 22 -m Refresh.Lte.putty 12.34.56.78
echo open 12.34.56.78> Import.Lte.ftp
echo cm>> Import.Lte.ftp
...
ftp -v -s:Import.Lte.ftp
cd D:\PATH\Sync\tables
unzip -o Tables.Lte.zip
cd D:\PATH\Sync\backend
sqlcmd -t 600 -d ATLAS -i Import.Lte.build.sql -o Import.Lte.sql
sqlcmd -t 600 -d ATLAS -i Import.Lte.sql -o Import.Lte.log
start D:\PATH\Harmony\backend\ConsistencyCheck.bat
start D:\PATH\Compass\backend\ConsistencyCheck.bat
cd D:\PATH\MAIL\XXXXX
mail.exe -s "Atlas Synchronized" -f "D:\PATH\Sync\backend\Import.Lte.log" -b [email protected] [email protected]
ConsistencyCheck.bat
This batch is started from Sync.Lte.bat
asynchronously. It uses the same credentials as the user running the root batch program.
D:
cd D:\PATH\Harmony\backend
sqlcmd -t 10800 -d ATLAS -i ConsistencyCheck.sql -o ConsistencyCheck.log
ConsistencyCheck.log
Sync.Lte.bat
runs just fine, and the email at the end is sent. But in ConsistencyCheck.log
, I find the following errors:
Cannot open database "ATLAS" requested by the login. The login failed.
Login failed for user 'XXXXX\admincm'.
- I have made both
XXXXX\admincm
andadmincm
db_owner of ATLAS. - All programs called from the scheduled task are run under the same user. All other programs (including
sqlcmd
calls to ATLAS) authenticate just fine, but thestart
ed programs do not. - When running
ConsistencyCheck.bat
manually (interactive RDP session) as another user, it runs fine. - When running
ConsistencyCheck.bat
manually asXXXXX\admincm
, it runs fine. - When scheduling a test from
XXXXX\admincm
in a couple of minutes (making sure I'm fully logged out of that user in an attempt to emulate normal environment), it runs fine. - When the original scheduled task runs at night, the async'd batches fail with the mentioned errors above.