I have a job in which one step is of type cmdexec, Agent history shows the step ran successfully with duration is 00:00:00, where as nothing was executed from the BAT file. If I run the step manually it works fine but when it is executed through it runs successfully without really executing commands of batch file. This job was running fine for over two years.
I restarted the Agent it started working fine.
Its on sql 2000 with sp3a.
What could be the reason?
Both the log files are empty (Step log and batch file log  , thats the issue. no place to see what exactly happened.
Step history shows
Executed as user: XXX\XXXXX. The step did not generate any output. Process Exit Code 0. The step succeeded.
I found out that couple of other jobs which had BAT file also behaved in same manner.
Is it somthing to do with env. or SQL Agent. Will it make a difference if I change file from BAT to CMD? But why... jobs were working fine since wo years...
When I restarted the AGENT all jobs ran fine. So its clear that permission is not the issue.
CMDEXEC step from all the jobs which were executing BAT file run successfully with duration as 0 and below mentioned message was in the step history
"Executed as user: DOMAIN\SQLService. The step did not generate any output. Process Exit Code 0. The step succeeded."
To run the batch file we use UNC path with Hidden share i.e.
When we run the batch file from command prompt it works fine with the same account with which agent is running with. But not from agent. When we restarted agent it restarted working again. The batch was working fine for past two year.
This is the command what is schedule using agent
\\APPServer\cr$\LIVE\7234\serverapps\batch\bin\DoLoad.bat -f CI -q
Conntents of DoLoad.bat
SET PERL5LIB=if "%OS%" == "Windows_NT" goto WinNTif exist "D:\CR\LIVE\7234\serverapps\batch\perl\bin\perl.exe" "D:\CR\LIVE\7234\serverapps\batch\perl\bin\perl.exe" -S "D:\CR\LIVE\7234\serverapps\batch\bin\DoBatch.pl" %*if not exist "D:\CR\LIVE\7234\serverapps\batch\perl\bin\perl.exe" perl.exe -S "DoBatch.pl" %*goto endofperl:WinNTif exist "D:\CR\LIVE\7234\serverapps\batch\perl\bin\perl.exe" "D:\CR\LIVE\7234\serverapps\batch\perl\bin\perl.exe" -S "D:\CR\LIVE\7234\serverapps\batch\bin\DoBatch.pl" %1 %2 %3 %4 %5 %6 %7 %8 %9if not exist "D:\CRr\LIVE\7234\serverapps\batch\perl\bin\perl.exe" perl.exe -S "DoBatch.pl" %1 %2 %3 %4 %5 %6 %7 %8 %9:endofperl
It happened again today... will applying SP4 will help...
You could try adding the following to the execution command line:
>> some_error_file_path_and_name.txt 2>>&1
This will capture any standard output from the command in addition to any error text thrown.
I would also temporarily comment out the @ECHO OFF just to see how things are actually excuting.
Environment variables, PATH particularly, can be bothersome depending on the environemtn and user. I would also add a SET statement right after the @ECHO statement.
One final point - one of the lesser known Windows things, the file qualifier of .BAT causes the OS top execute the file as a 16 bit application in a shared 16 bit pool. Changing the file qualifier to .CMD tells the OS to execute as aq 32 bit application with all of the address and memory protections not afforded to a 16 bit application.