Post reply

Capturing error message in Exec()

  • September 8, 2008 at 7:36 am

    #71082

    Hi all ,

    i am using the following code generate DAT file out of my data in table and when generating DAT if any error occurs i am trying to store it in a temp table but this giving 'Line 1: Incorrect syntax near 'SELECT getdate()' .in my actual query i dont have getDate() but i had a select query of a table even there same issue.

    if i replace the exec() by plain exec master.dbo.xp_cmdshell i get no error but in that case i cannot capture the error. pls help

    QUOTED_IDENTIFIER off

    drop table #ErrFile

    DECLARE@cmd VARCHAR(1000), @ExecError INT

    CREATE TABLE #ErrFile (ExecError INT)

    SET @cmd = 'exec master.dbo.xp_cmdshell bcp "SELECT getdate()" queryout "E:\BULKUPLOAD.dat" -c -U sa -P password '+' INSERT #ErrFile VALUES(@@ERROR)'

    EXEC(@cmd)

    SET @ExecError = (SELECT * FROM #ErrFile)

    SELECT @ExecError AS '@@ERROR'

    declare @ErrMsg varchar(1000)

    select @ErrMsg = [description] from master.dbo.sysmessages where error = @ExecError

    print @ErrMsg

    Thanks,

    Prem

  • September 8, 2008 at 8:35 am

    #868486

    You need to wrap this:

    bcp "SELECT getdate()" queryout "E:\BULKUPLOAD.dat" -c -U sa -P password

    in a pair of single quotes like this:

    ''bcp "SELECT getdate()" queryout "E:\BULKUPLOAD.dat" -c -U sa -P password''

    Remember that is a PAIR of single quotes, NOT a double quote.

    This is because xp_cmdshell is expecting a string.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • September 8, 2008 at 10:26 am

    #868584

    thank you!!! that worked:cool:

  • September 11, 2008 at 5:03 am

    #870218

    That issueis solved but at any point of time the #ErrFile contains only' 0' as the value,irrespective of any error happens during the master procedure exectuion. :blink:

    What could be the problem.Pls help

    Thanks,

    Prem

  • September 11, 2008 at 6:29 am

    #870260

    I don't think, based on BOL, that xp_cmdshell will ever return an @@ERROR code. If the command being executed by xp_cmdshell fails then the return value of xp_cmdshell will be 1. I think what you want to do is capture the return value and put that into your temp table. Something like this:

    DECLARE @cmd VARCHAR(1000), @ExecError INT

    CREATE TABLE #ErrFile (ExecError INT)

    SET @cmd = 'Declare @retval int; exec @retval = master.dbo.xp_cmdshell ''bcp "SELECT getdate()" queryout "E:\BULKUPLOAD.dat" -c -U sa -P password'' '+' INSERT #ErrFile VALUES(@REtval)'

    EXEC(@cmd)

    SET @ExecError = (SELECT * FROM #ErrFile)/code]

    You won't be able to get an error message, but you'll know the process failed.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

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

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