Post reply

xp_cmdshell

  • September 20, 2006 at 11:46 am

    #90496

    Hi,

    how to capture any error message when xp_cmdshell is executed.

    Sometimes the xp_cmdshell shows timeout expired related errors - how to capture this error message in the email notification.

    I tried to set a variable to the xp_cmdshell - but this returns either 0 or 1 depending on success or failure. To get the error message itself - how is it possible in sql server 2000.

    Please let me know regarding this.

    Thanks.

  • September 21, 2006 at 2:52 am

    #661663

    What we do is to insert the results of xp_cmdshell into a table then check the table for error messages.

    It's not a perfect solution, as you have to do string matching and know what kind of error messages you expect, but it's better than nothing.

    CREATE

    TABLE #cmdShellOutput (

    outputText VARCHAR(8000)

    )

    INSERT

    INTO #cmdShellOutput

    EXEC master..xp_cmdshell 'DIR E:\BadDir'

    SELECT

    * FROM #cmdShellOutput WHERE outPutText like 'File Not Found%'

    DROP

    TABLE #cmdShellOutput

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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