none
Catching Error Message from XP_CMDSHELL

    Question

  • I am running the following command: 

     EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline

    On the Results tab I get 2 lines
     Could not find a part of the path '\\server\directory\filename'.
     NULL

    How do I capture the first line in an error message?  I tried using a Try Catch block with "SELECT @ErrorMessage = ERROR_MESSAGE()" and it doesn't grab it.

     

    The message is not coming from sys.messages.  Where is this error message coming from then? 

     


    lcerni
    Wednesday, September 22, 2010 2:01 PM

Answers

  • The error comes from the command shell itself, it is not a SQL Server error

    one more way to grab the error is

    declare @cmdline varchar(500),@ReturnCode int 
    select @cmdline = 'dir f:' 
     
    create table #temp (SomeCol varchar(500)) 
     
     
    insert #temp 
    EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline 
     
    select * from #temp 
    where SomeCol is not null

    but of course if you do DIR C: the table will be filled with all the files and folders from that command

     

     

    HTH

    • Marked as answer by lcerni Thursday, September 23, 2010 1:25 PM
    Wednesday, September 22, 2010 3:05 PM
  • I guess you asked this question on another board too as I see Denis Gobo posted a new blog today

    How to capture the error output from xp_cmdshell in SQL Server


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, September 22, 2010 10:32 PM

All replies

  • How about:

    create

     

    table XPCmdShellOutput (OutputLine varchar(1000))

    Insert

     

    into XPCmdShellOutput Execute master..xp_cmdshell 'dir C::*.*'

    Then you could just select rows from the XPCmdSHellOutput table (perhaps only rows containing 'ERROR') and delete the table when done.

    For more details see:  http://www.sqlservercentral.com/articles/Error+Handling/69196/


    SQL DBA
    Wednesday, September 22, 2010 2:29 PM
  • The error comes from the command shell itself, it is not a SQL Server error

    one more way to grab the error is

    declare @cmdline varchar(500),@ReturnCode int 
    select @cmdline = 'dir f:' 
     
    create table #temp (SomeCol varchar(500)) 
     
     
    insert #temp 
    EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline 
     
    select * from #temp 
    where SomeCol is not null

    but of course if you do DIR C: the table will be filled with all the files and folders from that command

     

     

    HTH

    • Marked as answer by lcerni Thursday, September 23, 2010 1:25 PM
    Wednesday, September 22, 2010 3:05 PM
  • In addition to the other posts, note that the return value from xp_cmdshell will be non-zero, if the last command executed yielded an error.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Wednesday, September 22, 2010 10:25 PM
  • I guess you asked this question on another board too as I see Denis Gobo posted a new blog today

    How to capture the error output from xp_cmdshell in SQL Server


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, September 22, 2010 10:32 PM
  • Yes!  I have implemented this example and it works for me.  Thank you!

    Here is an example of what I put together for my procedure.  @RC and @EM would be the OUTPUT parameters of my procedure.

    DECLARE @cmdline               VARCHAR(1000)
    DECLARE @RC                      INT                     
    DECLARE @EM                      VARCHAR(8000)            

    DECLARE @temp TABLE (RM VARCHAR(500));


    SELECT @cmdline = 'dir f:'
     
    INSERT @temp
      EXEC @RC = master.dbo.xp_cmdshell @cmdline
     
    SELECT @EM = RM FROM @temp WHERE RM IS NOT NULL

    PRINT @RCode
    PRINT @EM

     


    lcerni
    Thursday, September 23, 2010 1:22 PM