Click or drag to resize

SA0146 : The RAISERROR statement with severity above 18 and requires WITH LOG clause

The topic describes the SA0146 analysis rule.

Message

The RAISERROR statement with severity above 18 and requires WITH LOG clause

Description

The rule checks RAISERROR statements for having severity above 18 and not having a WITH LOG clause.

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

Severity levels from 0 through 18 can be specified by any user.

Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.

For severity levels from 19 through 25, the WITH LOG option is required.

SQL
1RAISERROR('Invalid Type ''%s''',19, 22, 'type');

Should be:

SQL
1RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found next to the source of violation.

IGNORE:SA0146

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information
Example Test Script
SQL
 1RAISERROR('Invalid Type ''%s''',19, 22, 'type') ;
 2
 3
 4RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG,NOWAIT,SETERROR;
 5
 6RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH LOG;
 7
 8RAISERROR('Invalid Type ''%s''',18, 22, 'type') ;
 9
10RAISERROR('Invalid Type ''%s''',19, 22, 'type') WITH NOWAIT,SETERROR;

Analysis Results

 MessageLineColumn
1SA0146 : Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.10
2SA0146 : Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.100
See Also

Other Resources