SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Quick Hints for using the RAISERROR Command


Quick Hints for using the RAISERROR Command

Author
Message
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16520 Visits: 3403
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpoole/quickhintsforusingtheraiserrorcommand.asp

LinkedIn Profile
www.simple-talk.com
Sean Fackrell
Sean Fackrell
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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.


mccool
mccool
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 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





Chris Roesener
Chris Roesener
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 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.
Aaron Dutton
Aaron Dutton
SSC Veteran
SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)SSC Veteran (245 reputation)

Group: General Forum Members
Points: 245 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!





Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16520 Visits: 3403
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
www.simple-talk.com
John Scarborough
John Scarborough
SSC-Addicted
SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)SSC-Addicted (454 reputation)

Group: General Forum Members
Points: 454 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
noeld
noeld
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22314 Visits: 2048

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
Richard Moldwin
Richard Moldwin
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 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>


noeld
noeld
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22314 Visits: 2048
Richard: Nice rant


* Noel
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search