Sp_addmessage Problem!!!!

Apr 3, 2008

hello everyone.... i got this message... Msg 15043, Level 16, State 1, Procedure sp_addmessage, Line 137
You must specify 'REPLACE' to overwrite an existing message.

what that mean?? i dont understand... and below is Procedure coding...

quote:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [sys].[sp_addmessage]
@msgnum int = null,-- Number of new message.
@severity smallint = null,-- Severity of new message.
@msgtext nvarchar(255) = null,-- Text of new message.
@lang sysname = null,-- language (name) of new message
@with_log varchar(5) = null,-- Whether the message will ALWAYS go to the NT event log
@replace varchar(7) = null-- Optional parameter to specify that
-- existing message with same number should be overwritten.
as
declare @retcode int
declare @langidsmallint,
@msglangid smallint
declare @islog bit

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

if @msgnum is null or @severity is null or @msgtext is null
begin
raiserror(15071,-1,-1)
return (1)
end

-- User defined messages must be > 50000.
if @msgnum <= 50000
begin
raiserror(15040,-1,-1)
return (1)
end

-- Valid severity range for user defined messges is 1 to 25.
if @severity not between 1 and 25
begin
raiserror(15041,-1,-1)
return (1)
end

-- Verify the language
if @lang is not null
begin
-- Check to see if this language is in Syslanguages.
if not exists (select * from sys.syslanguages where name = @lang or alias = @lang)
and @lang <> N'us_english'
begin
raiserror(15033,-1,-1,@lang)
return (1)
end
end
else
select @lang = @@language

-- Get langid from syslanguages; us_english won't exist, so use 0.
select @langid = langid, @msglangid = msglangid
from sys.syslanguages where name = @lang or alias = @lang

select @langid = isnull(@langid, 0)
select @msglangid = isnull(@msglangid, 1033)

-- Set the event log bit accordingly
select @islog = (case rtrim(upper(@with_log))
when 'TRUE' then 1
when 'FALSE' then 0
end)
if @islog is null
begin
-- @with_log must be 'TRUE' or 'FALSE' or Null
if not (@with_log is null)
begin
raiserror(15271,-1,-1)
return (1)
end
if @langid = 0-- backward compatible
select @islog = 0
end

if @replace is not null
begin
if lower(@replace) <> 'replace'
begin
raiserror(15043,-1,-1)
return (1)
end
end

BEGIN TRANSACTION
SAVE TRANSACTION SP_ADDMESSAGE_INTERNAL
-- If this message not exists, lock ID anyway
EXEC %%ErrorMessage(ID = @msgnum).Lock(Exclusive = 1)

-- If we're adding a non-us_english message, make sure the us_english version already exists.
if (@langid <> 0) and not exists (select * from sys.messages$ where message_id=@msgnum and language_id = 1033)
begin
ROLLBACK TRANSACTION SP_ADDMESSAGE_INTERNAL
COMMIT TRANSACTION
raiserror(15279,-1,-1,@lang)
return(1)
end

-- If we're adding a non-us_english message, make sure that the severity matches that of the us_english version
if (@langid <> 0 ) and not exists (select * from sys.messages$ where message_id=@msgnum and severity=@severity and language_id = 1033)
begin
ROLLBACK TRANSACTION SP_ADDMESSAGE_INTERNAL
COMMIT TRANSACTION
declare @us_english_severity smallint
select @us_english_severity = severity from sys.messages$ where message_id=@msgnum and language_id = 1033
raiserror(15304,-1,-1,@lang,@us_english_severity)
return (1)
end

-- Warning: If we're adding a non-us_english message, ignore @with_log
if not (@islog is null) and (@langid <> 0)
raiserror(15042,-1,-1)

-- Does this message already exist?
if exists (select * from sys.messages$ where message_id=@msgnum and language_id=@msglangid)
begin
-- if so, are we REPLACEing it?
if lower(@replace) = 'replace'
begin
EXEC %%ErrorMessage(ID = @msgnum).RemoveMessage(LanguageID = @msglangid)
-- Sync non-us_english msg severity and eventlog with us_english version
if @langid = 0
begin
EXEC %%ErrorMessage(ID = @msgnum).SetSeverity(Severity = @severity)
EXEC %%ErrorMessage(ID = @msgnum).SetEventLog(EventLog = @islog)
end
end
else
begin
ROLLBACK TRANSACTION SP_ADDMESSAGE_INTERNAL
COMMIT TRANSACTION
-- The 'replace' option wasn't specified and a msg. with the number already exists.
raiserror(15043,-1,-1)
return(1)
end
end
else
begin
-- initialize us_english version
if @langid = 0
EXEC %%ErrorMessage().NewError(ID = @msgnum, Severity = @severity, EventLog = @islog)
end

-- Update/replace the message
EXEC %%ErrorMessage(ID = @msgnum).NewMessage(LanguageID = @msglangid, Description = @msgtext)
COMMIT TRANSACTION

return (0) -- sp_addmessage





what exactly i shud do?? everyone tell me...

View 12 Replies


ADVERTISEMENT

Developers Cannot Execute Sp_addmessage

Jun 6, 2006

Hi,

I have problems with the concept of user-defined messages.
I'm migrating from sybase ase to sql2005 and found out that there is only one global pool of messages.
Do you give every project on this server a dedicated range of error-numbers?

Another problem: only the sysadmin oder serveradmin can call sp_addmessage.
In my environment the developers own their databases and used to be able to create their own messages.

Now I try to let the developers call sp_addmessage by calling a "wrapper-procedure" which gets the needed rights.

create procedure sp__addmessage(@msgnum int, @severity smallint, @msgtext nvarchar(510), @lang sysname )WITH execute as OWNERASselect 'I am ' + suser_name(), user_name()exec sp_addmessage @msgnum, @severity, @msgtext, @lang gogrant execute sp__admessage to testusergo


When executing it as normal user (without sysadmin/serveradmin-roles) the output is:
I am sa dboMsg 15247, Level 16, State 1, Procedure sp_addmessage, Line 18User does not have permission to perform this action.


Isn't it strange?
Do you have a different idea how users/developers can create messages?

Thx

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved