Post reply

DTS and xp_cmdshell returns success even when fails?

  • January 12, 2007 at 4:25 am

    #92064

    We use a .bat file run thru win2k3 scheduler to copy data down via http://FTP.This occasionally fails (network issues?) so no data is available in the morning. You cannot schedule this to "retry". My thought is use a DTS job to execute the .bat file and retry if it fails.

    In testing this works fine, file is downloaded. However, when i try to break it(eg file isnt there), the job still returns successful...? I have run this in query analyser:

    DECLARE @result int

    EXEC @result = xp_cmdshell 'D:\scripts\getdata.bat'

    IF (@result = 0)

    PRINT 'success'

    ELSE

    PRINT 'fail'

    The @result returns as expected - 0 for success, 1 for failure - does anyone have any ideas why the job is still resulting as successful..??

  • January 12, 2007 at 6:42 am

    #682889

    Is there a reason that you are not using the execute process task?  I find that when the batch file returns an error, the execute process shows an error.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • January 16, 2007 at 7:10 am

    #683461

    hi, i am using SQL 2000, not 2005.

  • January 16, 2007 at 7:28 am

    #683468

    I found the execute process task in SQL 2000 DTS.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • January 16, 2007 at 11:15 am

    #683564

    The job is successful. It runs without an error.

    The execute process task will do better. The other thing you can do is "Raiserror" and that can cause a failure.

Viewing 5 posts - 1 through 4 (of 4 total)

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