Sp_addmessage Problem!!!!
Apr 3, 2008hello 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...