Microsoft SQL Server forum - sp_add_message - Internals Mystery

Author

sp_add_message - Internals Mystery - Help!!!!

andyblum@gmail.com

2006-04-07, 8:25 pm

Due to legacy issues we want to modify sp_addmessage. I am able to
open it it up change the code I want to add but it wont compile.

All I am doing is changing:

-- Must be ServerAdmin to manage messages
if is_srvrolemember('se
rveradmin') = 0
begin
raiserror(15247,-1,-1)
return (1)
end

To this code:

if (not (is_srvrolemember('s
ysadmin') = 1)) and ((@severity > 18) or
(rtrim(upper(@with_l
og)) = 'TRUE'))
begin
raiserror(15042,-1,-1)
return (1)
end

When we recompile I get:

Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 99
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 131
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 135
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 136
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 152
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addmessage, Line 156
Incorrect syntax near '%'.

When we goto that line I see:

EXEC %%ErrorMessage(ID = @msgnum).Lock(Exclusive = 1)

What is the "%%" doing - Does this make it impossible to modify?

Thanks,

Andy

Erland Sommarskog

2006-04-07, 8:25 pm

(andyblum@gmail.com) writes:
> When we goto that line I see:
>
>
> EXEC %%ErrorMessage(ID = @msgnum).Lock(Exclusive = 1)
>
>
> What is the "%%" doing - Does this make it impossible to modify?

That is some special syntax that works only in system procedures, which
is not availble to us user. Or perhaps it is, given the right settings
and environments. But it would be entirely unsupported to use it.

Furthermore, system procedures no longer live in the master database,
but in the hidde nmssqlsystemresource
database. That database is
intended to be readonly. When Microsoft ships a service pack of a hotfix
with changes in system procedures, they just replace the resource database,
so if you were able to make changes system procedures, you would lose
them with a service pack.

It looks as if you will have to change your routines, so that in the
future, only sysadmin users can add messages.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links