]
07-31-2023, 09:44 AM
**The [raiserror](
raiserror('Oh no a fatal error', 20, -1) with log
This will terminate the connection, thereby stopping the rest of the script from running.
Note that both severity level 20 or higher and the `WITH LOG` option are necessary for it to work this way.
This even works with GO statements, eg.
print 'hi'
go
raiserror('Oh no a fatal error', 20, -1) with log
go
print 'ho'
Will give you the output:
hi
Msg 2745, Level 16, State 2, Line 1
Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Oh no a fatal error
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Notice that 'ho' is not printed.
CAVEATS:
- This only works if you are logged in as admin ('sysadmin' role), and also leaves you with no database connection.
- If you are NOT logged in as admin, the RAISEERROR() call itself will fail *and the script will continue executing*.
- When invoked with sqlcmd.exe, exit code 2745 will be reported.
Reference: [
**The noexec method**
Another method that works with GO statements is `set noexec on` ([docs][2]). This causes the rest of the script to be skipped over. It does not terminate the connection, but you need to turn `noexec` off again before any commands will execute.
Example:
print 'hi'
go
print 'Fatal error, script will not continue!'
set noexec on
print 'ho'
go
-- last line of the script
set noexec off -- Turn execution back on; only needed in SSMS, so as to be able
-- to run this script again in the same session.
[1]:
[To see links please register here]
) method**raiserror('Oh no a fatal error', 20, -1) with log
This will terminate the connection, thereby stopping the rest of the script from running.
Note that both severity level 20 or higher and the `WITH LOG` option are necessary for it to work this way.
This even works with GO statements, eg.
print 'hi'
go
raiserror('Oh no a fatal error', 20, -1) with log
go
print 'ho'
Will give you the output:
hi
Msg 2745, Level 16, State 2, Line 1
Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Oh no a fatal error
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Notice that 'ho' is not printed.
CAVEATS:
- This only works if you are logged in as admin ('sysadmin' role), and also leaves you with no database connection.
- If you are NOT logged in as admin, the RAISEERROR() call itself will fail *and the script will continue executing*.
- When invoked with sqlcmd.exe, exit code 2745 will be reported.
Reference: [
[To see links please register here]
][1]**The noexec method**
Another method that works with GO statements is `set noexec on` ([docs][2]). This causes the rest of the script to be skipped over. It does not terminate the connection, but you need to turn `noexec` off again before any commands will execute.
Example:
print 'hi'
go
print 'Fatal error, script will not continue!'
set noexec on
print 'ho'
go
-- last line of the script
set noexec off -- Turn execution back on; only needed in SSMS, so as to be able
-- to run this script again in the same session.
[1]:
[To see links please register here]
[2]:[To see links please register here]