The sqlcmd statement :on error exit instructs sqlcmd to immediately stop executing a script when an SQL or a T-SQL statement is erroneous.
This behaviour is demonstrated with the following simple example script:
:on error exit
drop view if exists tq84_vw_one;
drop view if exists tq84_vw_two;
drop table if exists tq84_tab_one;
drop table if exists tq84_tab_two;
print "creating tq84_tab_one";
go
create table tq84_tab_one (foo integer);
go
print "creating tq84_vw_one";
go
create view tq84_vw_one as select 2 * foo as foo_twice from tq84_tab_one;
go
print "creating tq84_vw_two";
go
create view tq84_vw_two as select 2 * foo as foo_twice from tq84_tab_two
go
print "creating tq84_tab_two";
go
create table tq84_tab_two (foo integer);
go
When the script is run, it cannot create the view tq84_vw_two because the underlying table tq84_tab_two does not exist. Because of the :on error exit statement, the script prints, when executed, the following:
P:\ath\to\script> sqlcmd -d … -i on-error-exit.sql
creating tq84_tab_one
creating tq84_vw_one
creating tq84_vw_two
Msg 208, Level 16, State 1, Server TQ84-PC, Procedure tq84_vw_two, Line 2
Invalid object name 'tq84_tab_two'.
It is possible to overwrite this behaviour with the -V command line option of sqlcmd which allows to specify a minimum error severity level. As the severity level of the failed create view statement is 16, we specify a number greater than that. With -V20, the script is not aborted (although the view is not created):
P:\ath\to\script> sqlcmd -d … -i on-error-exit.sql -V 20
creating tq84_tab_one
creating tq84_vw_one
creating tq84_vw_two
Msg 208, Level 16, State 1, Server TQ84-PC, Procedure tq84_vw_two, Line 2
Invalid object name 'tq84_tab_two'.
creating tq84_tab_two
See also
The behaviour of :on error goto can also be specified on the command line with the -b option.