Wednesday, January 25, 2012 12:22 PM
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
Thursday, January 26, 2012 8:25 AM
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.
- Edited by Ganesan P Thursday, January 26, 2012 8:29 AM
Friday, January 27, 2012 9:00 AM
Please can you help to find solution for it.
Friday, January 27, 2012 9:29 AMModerator
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)
TechNet Community Support
- Marked As Answer by Stephanie LvModerator Wednesday, February 01, 2012 8:59 AM
Friday, January 27, 2012 9:37 AM
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.