CODEWITHSUNDEEP
sql-serverbatch-filesqlcmd
Rod
Rod

Reputation: 15455

Capture error and continue with sqlcmd :r

I have the following batch file and SQL script that runs several .sql files. Is there a way to log any errors and continue with script?

myautosql.sql 

PRINT 'Starting...'

--:On Error exit

:r SQLQuery10.sql
:r SQLQuery20.sql
:r SQLQuery30.sql


PRINT 'Completed normally on ' + (CONVERT( VARCHAR(24), GETDATE(), 101))
GO

myautosql.bat 

SQLCMD -E -d rstestdb1 -i myautosql.sql
PAUSE

When I intentionally raiseerror in the SQLQuery20.sql file the batch program stops. Is there a way for it to log the error and continue with script?

Upvotes: 2

Views: 1762

Answers (1)

James L.
James L.

Reputation: 9451

When you raiserror, the 2nd parameter severity dictates whether the query will continue to run or not. A severity of 0-10 are informational (no error is raised), 11-19 are non fatal errors, 20-25 will raise the error and then immediately terminate your connection to the database server. You must be a sysadmin to use a severity from 19-25.

I think this simulates what you are trying to do.

auto.sql

PRINT 'Starting...'

:r 1.sql
:r 2.sql

PRINT 'Completed normally on ' + (CONVERT( VARCHAR(24), GETDATE(), 101))

1.sql

select 1 as value
raiserror ('This will not stop execution', 11, 1)
select 2 as value

2.sql

select 3 as value
raiserror ('This is an information message, not an error', 10, 1)
select 4 as value

Then you run the following command to capture the query output to output.txt and informational/error messages to error.txt:

sqlcmd -E -d tempdb -i auto.sql -r1 >output.txt 2>error.txt

-r1 tells sqlcmd to redirect informational/error messages to STDERR.

>output.txt redirects STDOUT from the queries (including affected row counts) to a file called output.txt.

2>error.txt redirects STDERR to a file called error.txt.

Here are the two files from the above script:

output.txt

value      
-----------
          1

(1 rows affected)
value      
-----------
          2

(1 rows affected)
value      
-----------
          3

(1 rows affected)
value      
-----------
          4

(1 rows affected)

error.txt

Starting...
Msg 50000, Level 11, State 1, Server NAME, Line 4
This will not stop execution
This is an information message, not an error
Completed normally on 02/27/2020

HTH

Upvotes: 1

Related Questions