Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: how does xp_cmdshell decide success vs. failure?

    Hi all,

    I have a stored proc that uses xp_cmdshell to boot off a batch file on the NT side of the box (box OS is Windows 2000 Advanced Server).

    Here is the pertinent code:
    Code:
    /*----- Kick off the NT bat job to suck over the data through the web service pipe			*/
    SELECT @NTCommand = 'D:TradeAnalysis\WondaDataStore\Jobs\PullFromWONDA_InstitutionalRankings.bat ' + CONVERT(varchar(10), @ReqDate, 101)
    EXECUTE @e_error = master.dbo.xp_cmdshell @NTCommand
    SELECT @m_error = CASE WHEN  ISNULL(@e_error, 0) <> 0 THEN (@e_error + 50000) ELSE @@Error END
    IF @m_error <> 0 GOTO ErrorHandler
    The trouble is that the batch file is failing (soft error, caught internally to the batch file, which then kills itself, screaming loudly all the way).

    The batch file is using the following "voice" in which to scream in pain as it dies (a.k.a., using this code to terminate itself, which kills the cmd shell and returns 13 as an error code)
    Code:
    REM WonDBService.exe says we failed
    Date /T
    Time /T 
    EXIT /B 13
    Meanwhile, back at the ranch (errr...back in stored procedure), what is being returned is a ZERO (in the first code block, @e_error is being set to ZERO when the xp_cmdshell returns from the bat file.

    So, now that I have ruled out the obvious *LOL* how can I get my xp_cmdshell to realize it has failed miserably at the one, tiny, simple, not-too-much-to-ask, job that it is designed to do?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    paul ... i've seen this in the past, and it seems as though xp_cmdshell feels that it has successfully completed its mission if it contacts the O.S. It looks like your batch job could write an event to the application log, and then have the O.S. send an alert based on that error code.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks, that's what I'm afraid of, but from BOL...
    Code:
    Note  When executing xp_cmdshell with the Microsoft&#174; Windows&#174; 98 operating systems, the return code from xp_cmdshell will not be set to the process exit code of the invoked executable. The return code will always be 0.
    does this not imply that NON Microsoft&#174; Windows&#174; 98 operating systems WILL get the return code from the exit code of the invoked executable???
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •