HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

raise an error in sql server severity higher than 18

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorsqlthanhigherraiseserverseverity

Problem

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.

Solution

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).

Code Snippets

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

Context

StackExchange Database Administrators Q#216154, answer score: 7

Revisions (0)

No revisions yet.