How to get the SQL Server script error in a batch File

Question

I am calling an SQL Server script on a batch file, but I need get the error(when the script fail) in the batch file, what can I do?

This is the batch file:

sqlcmd -S HOST  -U User -P password -i test.sql
echo %errorlevel%

and this is the Script File(Test.sql):

USE TrainingSitecore_Web  
go

SELECT * FROM   items 
go

RAISERROR ('This is a test Error.',-- Message text.
           16,-- Severity.
           1 -- State.
); 

Make sense?

Answer 1

It can be done using -r parameter:

Example:

sqlcmd -Q "select 1 as a; select 1/0 as b" -E -r1 1> NUL

-r[ 0 | 1] msgs to stderr

Redirects the error message output to the screen (stderr). If you do not specify a parameter or if you specify 0, only error messages that have a severity level of 11 or higher are redirected. If you specify 1, all error message output including PRINT is redirected. Has no effect if you use -o. By default, messages are sent to stdout.

MSDN for more