cmdexec step running and not doing anything

  • Hi,

    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?

    Thanks

    Deepak

     

  • Did you try including a log file to track what is happening when the job executes. That will give you a clue where the problem lies. Include the log file and let us know the log result.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Hi,

    Both the log files are empty (Step log  and batch file log&nbsp , 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.

     

  • Hi,

    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...

    Deepak

  • check for permission on sql registry keys and default installation paths. That might have caused the access issue.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • 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.

    \\APPServer\cr$\LIVE\7234\serverapps\batch\bin\DoLoad

    .bat

    Cheers

     

    Deepak

     

  • Could you post the bat/cmd file ?

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Hi,

    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

    ====================

    @echo off

    setlocal

    SET PERL5LIB=

    if "%OS%" == "Windows_NT" goto WinNT

    if 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

    :WinNT

    if 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 %9

    if 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

    endlocal

     

  • It happened again today... will applying SP4 will help...

     

    Deepak

     

  • 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. 

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Hi Rudy,

    We are capturing the output, unfortunately the log file is empty and the time stamp of the file is not changing, which makes be beleive that BAT file is not getting executed at all.

    In some  case the BAT file is been called from DTS package. Package log shows the step executed successfully  and time taken by job is 0 sec.

    Will change BAT to CMD for couple of jobs and will see how they behave as compared to BAT files.

    Thanks

    Deepak

  • I have seen simialar problems with sqlmaint and it is due to something happening to the .dll in memory, thats why restart of agent is re-setting it.

    Possible options:

    server reboot

    copy the DLL over from another server thats ok

    upgrade to SP4 could help you if this .dll is replaced by the upgrade

    ---------------------------------------------------------------------

  • Finally narrowed down the issue. The cause is PGP encryption. When the BAT file which encrypts or decrypts data fails. It corrupts Agents Command Shell.

    How to prevent it ?

    Deepak

  • .. Is the problem solved ?

  • Problem was not resolved , was living with it but found a way to get notified when jobs executes without actually running the BAT file.

    Schedule a job which has 4/5 step, each step calls a BAT File directly or indirectly like from SSIS pkg or SP which uses xp_cmdshell or CMDEXEC. When bat/ssis/sp runs, it inserts a record in a table. Last step of the job checks if it has 4/5 records in the table , which indicates bat/ssis/sp go executed successfully else an email is sent to support team to look into the issue.

    That was no the best solution but support team gets the msg to check and take necessary action.

    Deepak

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply