CODEWITHSUNDEEP
sqlsql-serversqlcmd
Newbie
Newbie

Reputation: 7249

How do we tell sqlcmd to continue "on error" with next batch?

Any ideas on how to accomplish this?

USE [db_name]
BEGIN TRANSACTION
...TONS OF INSERTS
COMMIT;
RAISERROR (..) WITH NOWAIT;  //If error continue with next batch

BEGIN TRANSACTION
...TONS OF INSERTS
COMMIT;
RAISERROR (..) WITH NOWAIT;

...

Upvotes: 8

Views: 15583

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294307

The behavior of batch interruption on error is a SQL Server (ie. backend) option and is governed by error severity. There is no way to change the server behavior, errors that interrupt the batch will always interrupt the batch, period.

The behavior of file continuation (run the next GO delimited batch after error) is a sqlcmd option and is controlled by the -b switch. By default is ON (meaning sqlcmd continues with the next batch).

Upvotes: 9

user1738833
user1738833

Reputation: 329

Use the -V flag on the command-line. If you set a sufficiently large enough value (e.g 17), even fairly severe errors will not stop the script.

e.g. sqlcmd ... -V 17 -i MyFile.sql

I will assume that you know what you're doing when you do this and are monitoring the error messages nonetheless.

You could go higher, up to level 25, but if you're getting errors between level 17 and 25, it's unlikely that you're going to be able to progress much because they tend to be caused by software or hardware errors on the server, rather than errors in the scripts you're inputting.

Upvotes: 14

Related Questions