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 @langid smallint,
@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

---

exec sp_addmessage 50005, 10, '%s', @with_log = true, @replace = 'REPLACE'

quote:

---

CREATE TABLE [dbo].[Users](
[User_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Users_User_application_id] DEFAULT (newid()),
[User_name] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[User_fname] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[User_lname] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[User_gender] [varchar](6) COLLATE Latin1_General_CI_AI NULL,
[User_address] [varchar](max) COLLATE Latin1_General_CI_AI NOT NULL,
[User_hp_no] [varchar](10) COLLATE Latin1_General_CI_AI NULL,
[User_home_no] [varchar](10) COLLATE Latin1_General_CI_AI NULL,
[User_email] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[User_last_activity] [datetime] NULL,
[User_password] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[User_name_Encrypted] [varbinary](max) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[User_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

---

quote:

---

ALTER trigger [TestTrigger] on
[dbo].[Users]
for insert
as
--Declare variable to hold message
Declare @Msg varchar(8000)
Declare @CmdString varchar (1000)
--Assign to message "Action/Table Name/Time Date/Fields inserted
set @Msg = 'Inserted | Users | ' + convert(varchar(20), getdate()) + ' | '
+(select convert(varchar(MAX), User_application_id)
+ ', ' + User_fname + ', ' + User_lname
from inserted)
--Raise Error to send to Event Viewer
raiserror( 50005, 10, 1, @Msg)
set @CmdString = 'echo ' + @Msg + ' >> C:\logtest.log'
--write to text file
exec master.dbo.xp_cmdshell @CmdString

---

quote:

---

INSERT INTO Users (User_name, User_fname, User_lname, User_gender, User_address, User_hp_no,
User_home_no, User_email, User_last_activity, User_password )
VALUES (N'q1', N'saya', N'dzul', N'Male', N'China', N'0129876544', N'0312398765',
N'Lin_Dan@yaho.com', getdate(), N'123');

---

Select @Msg = '"Inserted | Users | ' + convert(varchar(20), getdate()) + ' | ' 
+ convert(varchar(MAX), User_application_id) 
+ ', ' + User_fname + ', ' + User_lname + '"'
from inserted

set @CmdString = 'echo "' + @Msg + '" >> C:\logtest.log' 

quote:

---

The new user of "saya" is added.
The user address is "China"
"Inserted | Users | Apr 3 2008 5:23PM | 82FFA9C4-3DD4-44AE-A428-32B29459C0F7, saya, dzul"
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Access is denied.
NULL

---

quote:

---

ALTER procedure [sys].[sp_addmessage]

---

quote:

---

--write to text file
exec master.dbo.xp_cmdshell @CmdString

---