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