sqlcmd -b doesn't exit on error

Question

I'm confused how should I use -b parameter of sqlcmd. I'm trying to write simple automation script for my DB and wanted to stop execution in case of any errors. I'm using SQLServer 2008r2.

For testing I'm calling simple script

sqlcmd -b -i test.sql

content of test.sql:

PRINT 'test1'; 
RAISERROR ('Error raised', 15, 15);
PRINT 'test2';

I expected that after first print I would see error msg and script would stop. But every time second print is executed and I see following output:

test1
Msg 50000, Level 18, State 15, Server TOMEK, Line 3
Error raised
test2

What I'm doing wrong?

Answer 1

The -b parameter applies to the batch, not statements within the script. If you separate each statement with a GO batch terminator, you will get the behavior you expect.

Remember that SQLCMD sends the entire batch of statements at once to SQL Server for execution. You'll need T-SQL control flow statements (including TRY/CATCH) to handle errors on the server side, if needed.