none
SQl Server Error Code needs to be capture from DOS Batch Script

    Question

  • Hi Team,

    Is there any way to get the SQL server error message through the DOS Batch script?

    For example I have one procedure and it took around 60 seconds to complete. I need to call this procedure via one DOS batch script. The procedure will throw an error in the 50<sup>th</sup> second. That error needs to be capture and populate in DOS. In actual scenario the DOS batch script is just triggering the process and it is moving to the next line and it is not waiting for any further reply from the previous line.

    Thanks in advance

     

    Wednesday, January 25, 2012 12:22 PM

Answers

All replies

  • Hi Gurus,

    Please can you respond on it. Is it possible to capture Error Code in DOS which generated from Procedure.

    Below is scenario

    I have Batch script say Test.Bat

    In side the Bat script, I am calling a Proc "usp_test_proc" using SQLCMD command.

    In usp_test_proc, I handled the error via Raise_Application_Error. If I run the proc alone then I can see the errors.

    Now I want to capture this error in DOS. Please can you help how to achieve it.

    I understand it may be achievable if convert my complete proc on DOS script i.e. Write each statement from proc into BAT as my proc is quite big.

    Please advice if this can be achieve via calling Proc from SQLCMD.

    Thanks,

    • Edited by Ganesan P Thursday, January 26, 2012 8:29 AM
    Thursday, January 26, 2012 8:25 AM
  • Hi All,

    Please can you help to find solution for it.

    Thanks,

    Friday, January 27, 2012 9:00 AM
  • Hi Ganesan P,

    One possible method is to log the error message for the Stored Procedure to a txt file, and return the errors from the txt file after completing the batch file.

    Please pay attention to articles:
    Creating Log file for Stored Procedure

    For the second stored procedure PersonUpdate, you need to add the definition for the variable @Msg:

    declare @Msg varchar(500)
    
    

    batch file to read all lines from txt file


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Stephanie Lv Wednesday, February 01, 2012 8:59 AM
    Friday, January 27, 2012 9:29 AM
  • Hi Ganesan,

     

    Try this.

    1. Stored Procedure that raises an error:

    CREATE PROCEDURE uspTest
    AS
    RAISERROR ('', 11, 1)
    RETURN
    


    2. Batch file:

    @ECHO OFF
    
    SQLCMD -S <ServerName> -d <DatabaseName> -b -Q "exec dbo.uspTest"
    
    IF ERRORLEVEL 1 GOTO Error
    GOTO :NoError
    
    :Error
    Echo Error occured...
    GOTO :EOF
    
    :NoError
    Echo Success...
    GOTO :EOF
    


    Please note that error can be captured only if severity level is greater than 10.


    - Vishal

    SqlAndMe.com

    Friday, January 27, 2012 9:37 AM