Error Severity Levels Greater Than 18

Contents Raiserror Severity Levels

(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語)  HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Asked by: Error severity levels greater than 18 can only be specified by members of the sysadmin role, error severity levels greater than 18 can only be specified by members using the WITH LOG option. SQL Server > Transact-SQL Question 0 Sign in sql server error 2754 to vote Hi!I got the wrong message )-: BEGIN TRY

RAISERROR ( 'nineteen', 19, 1 )

END TRY

BEGIN msg 2754 level 16 state 1 CATCH

SELECT ERROR_MESSAGE()

END CATCH ;

How to solve that?Best RegardsBjornB. D. Jensen Friday, February 13, 2009 12:52 PM Reply | Quote All replies 0 Sign in to vote RAISERROR ('eighteen', 18, 1) ;)George Friday, February 13, 2009

Raiserror Severity Levels

12:55 PM Reply | Quote Answerer 0 Sign in to vote I want to catch errors with levels higher than 18...B. D. Jensen Friday, February 13, 2009 1:20 PM Reply | Quote 1 Sign in to vote DO it as Error message is saying BEGIN TRY     RAISERROR ( 'nineteen', 19, 1 )With log     END TRY      BEGIN CATCH     SELECT ERROR_MESSAGE()     END CATCH  Mangal PardeshiSQL With Mangal Technical Skill is the sql server raiserror mastery of complexity, while Creativity is the master of simplicity Friday, February 13, 2009 1:21 PM Reply | Quote Moderator 0 Sign in to vote  You need to read the Books Online entry for Try...Catch and Raiserror Raiserror - http://msdn.microsoft.com/en-us/library/ms178592(SQL.90).aspx severity    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. Try...Catch - http://msdn.microsoft.com/en-us/library/ms179296.aspxErrors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not closed. George Friday, February 13, 2009 1:28 PM Reply | Quote Answerer 0 Sign in to vote Hi!Of course I'm not really trying to raise error with level 19 myself.I just try to minimc some db behaviour and find out how to catchan error that is seen in another test situation where I tried an long runningsql that runs for 1+ hour telling something about LOCK i

Foreign KeysEX0007 : List all DML and DDL triggers in current databaseEX0009 : Consider adding proper comment block before each database object create statementEX0010 : Identify missing indexes using dynamic management views informationEX0011 : Identify inefficient indexes using dynamic management views informationEX0012 : Displays memory usage information for the current databaseEX0013 : Identify fragmented indexes that need rebuilding or re-indexingEX0014 : List the last execution status of all available SQL Server jobsEX0015 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5b2966eb-5e7b-41f8-8c8e-4817a50ccfb7/error-severity-levels-greater-than-18-can-only-be-specified-by-members-of-the-sysadmin-role-using?forum=transactsql : Find Best Clustered IndexEX0018 : Analyze execution plan and check for high cost operationsSA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULLSA0002 : Variable declared but never referenced or assignedSA0003 : Variable used but not previously assignedSA0004 : Variable assigned but http://www.ubitsoft.com/products/sqlenlight/help_19/html/59e4c618-4d12-40c1-b5c6-ae32904e09db.htm value never usedSA0005 : Non-ANSI outer join syntaxSA0006 : Non-ANSI inner join syntaxSA0007 : Pattern starting with "%" in LIKE predicateSA0008 : Deprecated syntax string = expression_aliasSA0009 : Consider using a table variable instead temporary tableSA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statementSA0011 : SELECT * in stored procedures, views and table-valued functionsSA0012 : Use SCOPE_IDENTITY() instead @@IDENTITYSA0013 : Avoid returning results in triggersSA0014 : Avoid 'fn_' prefix when naming functionsSA0015 : Avoid 'sp_' prefix when naming stored proceduresSA0016 : Use of very small variable length type (size 1 or 2)SA0017 : SET NOCOUNT ON option in stored procedures and triggersSA0018 : Support for constants in ORDER BY clause have been deprecatedSA0019 : TOP clause used in a query without an ORDER BY clauseSA0020 : Always use a column list in INSERT statementsSA0021 : Deprecated usage of table hints without WITH keywordSA0022 :

the IMPACT of the various severity levels that can be used by RAISERROR.TL;DR- 0 is basically just informational- 1 through 10 include message id / level / state- 11 through 16 are red, log immediately,and http://www.sbrickey.com/Tech/Blog/Post/SQL_RAISERROR_severity_levels will disrupt a TRY/CATCH block- 17 and 18 only log at the END of the http://www.sqlservercentral.com/Forums/Topic144081-8-1.aspx batch/procedure (does not stop execution, except in a TRY/CATCH block)- 19 and aboveare sysadmin only, require 'WITH LOG'- 20 and above will KILL the entire PROCESS (batch/procedure/etc)- above 25 is remapped to 25TESTING SCENARIOI am running these tests on SQL 2008 R2 with SP2(10.50.4000.0), Enterprise Edition, 64-bit.TEST 1 : Single Procedure : QueryPRINT 'SEVERITY 0' RAISERROR ( N'Message: 0' error severity , 0 , 1 ) PRINT CHAR(13) + 'SEVERITY 1' RAISERROR ( N'Message: 1' , 1 , 1 ) PRINT CHAR(13) + 'SEVERITY 9' RAISERROR ( N'Message: 9' , 9 , 1 ) PRINT CHAR(13) + 'SEVERITY 10' RAISERROR ( N'Message: 10' , 10 , 1 ) PRINT CHAR(13) + 'SEVERITY 11' RAISERROR ( N'Message: 11' , 11 , 1 ) PRINT CHAR(13) + 'SEVERITY 18' RAISERROR ( N'Message: 18' , 18 , 1 ) error severity levels GO PRINT CHAR(13) + 'SEVERITY 19' RAISERROR ( N'Message: 19' , 19 , 1 ) GO PRINT CHAR(13) + 'SEVERITY 19 WITH LOG' RAISERROR ( N'Message: 19' , 19 , 1 ) WITH LOG GO PRINT CHAR(13) + 'SEVERITY 20 WITH LOG' RAISERROR ( N'Message: 20' , 20 , 1 ) WITH LOG GO PRINT CHAR(13) + 'SEVERITY 25 WITH LOG' RAISERROR ( N'Message: 25' , 25 , 1 ) WITH LOG GO PRINT CHAR(13) + 'SEVERITY 30 WITH LOG' RAISERROR ( N'Message: 30' , 30 , 1 ) WITH LOG GOnotice that I started to need to include statement separators (GO), so that the errors correctly align to the order in which they are thrown (see TEST 3 below)TEST 1 : Single Procedure :ResultsSEVERITY 0 Message: 0 SEVERITY 1 Message: 1 Msg 50000, Level 1, State 1 SEVERITY 9 Message: 9 Msg 50000, Level 9, State 1 SEVERITY 10 Message: 10 SEVERITY 11 Msg 50000, Level 11, State 1, Line 10 Message: 11 SEVERITY 18 Msg 50000, Level 18, State 1, Line 12 Message: 18 SEVERITY 19 Msg 2754, Level 16, State 1, Line 15 Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option. SEVERITY 19 WITH LOG Msg 50000, Level 19,

up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 7,2000 » T-SQL » Msg 2754 from Transact-SQL Msg 2754 from Transact-SQL Rate Topic Display Mode Topic Options Author Message Frank RosserFrank Rosser Posted Saturday, October 30, 2004 10:03 PM SSC Rookie Group: General Forum Members Last Login: Thursday, February 16, 2012 7:28 PM Points: 34, Visits: 137 Does anyone know how to get rid of Msg 2754 when using RAISERROR with a Severity greater than 18?I keep getting the following. I know what it means, but I just can't see the point of it. I would rather it not appear on the error log.Msg 2754, Sev 16: Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option. [SQLSTATE 42000]Thanks. Post #144081 Phil ParkinPhil Parkin Posted Sunday, October 31, 2004 8:07 PM SSCertifiable Group: General Forum Members Last Login: Today @ 11:24 AM Points: 7,667, Visits: 18,505 Use this:raiserror('',20,1) with logwhich will terminate the client connection (severity > 19) and write to the error log.RegardsPhil Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.Just when you think your code is idiot-proof, the world invents a better idiot. Post #144095 Frank RosserFrank Rosser Posted Monday, November 1, 2004 11:07 PM SSC Rookie Group: General Forum Members Last Login: Thursday, February 16, 2012 7:28 PM Points: 34, Visits: 137 It turned out to be a missing WITH LOG clause. There was confusion over where the error was been raised - the calling procedure, or