Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Quick Hints for using the RAISERROR Command Expand / Collapse
Author
Message
Posted Wednesday, October 12, 2005 4:39 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 2,916, Visits: 1,854
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/quickhintsforusingtheraiserrorcommand.asp

LinkedIn Profile
Newbie on www.simple-talk.com
Post #228491
Posted Wednesday, November 23, 2005 3:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 7, 2007 1:13 AM
Points: 30, Visits: 1

I think if you use RAISERROR WITH LOG in your production stored procedures, users executing the procedure that aren't members of the sysadmin role will cause the procedure to fail so this is a limitation of its use in this scenario.  You can get round this by using a debug or trace bit parameter and only calling RAISERROR if this parameter is set to 1.  This may not always be possible in a production environment unless you are able to change the client calling code to switch the trace on/off.

Post #239107
Posted Wednesday, November 23, 2005 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 6, 2006 2:08 PM
Points: 11, Visits: 1

Lots of extra commas were showing up in the first example for some reason.  It should read:

 

DECLARE @sStringVar VARCHAR(440) ,
 @lInteger Int ,
 @fFloat  decimal(6,2) ,
 @byUnsignedInt tinyint
SELECT @sStringVar = 'A string' ,
 @lInteger=44 ,
 @fFloat=3.14 ,
 @byUnsignedInt = 56
RAISERROR('The string variable contains %s, the int contains %d, the float contains %d, the uint contains %d',
 10 -- Informational severity only.
 ,1
 ,@sStringVar,
 @lInteger,
 @fFloat,
 @byUnsignedInt
) WITH LOG, NOWAIT




Post #239158
Posted Wednesday, November 23, 2005 7:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 12:02 PM
Points: 169, Visits: 101
Nice article.  To Sean's point -- you could wrap the calls with IF IS_SRVROLEMEMBER('sysadmin') = 1 to prevent problems with the user not having sysadmin.
Post #239164
Posted Wednesday, November 23, 2005 9:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 8, 2012 4:20 PM
Points: 225, Visits: 88

Thanks for the enlightening article about RAISEERROR.  I just wanted to point out a couple things.

I think there's a typo in the very last example.  The line is:

SET @sMessage = CONVERT(CHAR(19),@dtEnd,120) + ' <proc name>:<block description> started'

But it should be:

SET @sMessage = CONVERT(CHAR(19),@dtStart,120) + ' <proc name>:<block description> started'

Also, I think you can get the current stored procedure name using:

 object_name(@@PROCID)

So you wouldn't have to hardcode it in your literal.

Cheers!




Post #239202
Posted Wednesday, November 23, 2005 10:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 2,916, Visits: 1,854
Thanks, Old SQL 6.5 habits die hard. Although I know it exists I rarely use the object_name function.

Does anyone know the correct syntax for getting decimal places to appear in the RAISERROR statements? As I said in the article BOL says you can do it but I simply couldn't get it to work.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #239219
Posted Wednesday, November 23, 2005 10:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:52 PM
Points: 290, Visits: 52
Very nice article. Short, simple, useful. A few typos help me think about what I'm doing, so I won't complain about those!

John Scarborough
MCDBA, MCSA
Post #239222
Posted Wednesday, November 23, 2005 12:10 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031

That is not entirely true. If the severity level used in the RAISERROR WITH LOG is between 0 and 18 you don't need to be part of the sysadmin role but if the value is between 19 and 25 you must be sysadmin

Good article by the way

 




* Noel
Post #239249
Posted Wednesday, November 23, 2005 12:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 28, 2013 1:27 PM
Points: 116, Visits: 15

Nice article.  It's just too bad that there is a need for it.

<Rant On>

Error handling in SQL Server 2000 is utterly brain-dead.  Whenever I can, I prefer to code in DMO/ADO just to avoid this "little" issue, and the equally brain-dead SQL debugger.  Unfortunately, the DMO work-around is often inadequate or too slow, forcing my hand to use kludges like this.  T-SQL programming involves a major loss of productivity, compared to any other modern language I know of.

<Rant off>

Post #239268
Posted Wednesday, November 23, 2005 1:45 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
Richard: Nice rant


* Noel
Post #239288
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse