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^th 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

 

Answer 1

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,

Answer 2

Hi All,

Please can you help to find solution for it.

Thanks,

Answer 3

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

Answer 4

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