SQL Server – Raising Errors with Severity Higher Than 18

errorsraiserrorsql server

I can easily raise user defined errors where severity is not higher than 18. When is higher, I get this error

Error severity levels greater than 18 can only be specified by members of the
sysadmin role, using the WITH LOG option.

I have access to sysadmin role, so can someone specify the syntax for raising errors WITH LOG option.
Thanks.

Best Answer

WITH is an optional clause that goes at the end of the RAISERROR. A few examples:

RAISERROR('Oops', 10, 1)
RAISERROR('Oops', 10, 1) WITH NOWAIT
RAISERROR('Oops', 10, 1) WITH SETERROR

RAISERROR('Oops', 19, 1) -- Fails with "Error severity levels greater than 18..."
RAISERROR('Oops', 19, 1) WITH LOG -- OK

Remember that severities of 20 and above will terminate the connection (won't even jump to a CATCH).

Related Question