swapnilsardeshpande
asked on
How to store SQL Server error message in a table
I'm writing one procedure say sp_driver_proc which gives call to procedures proc1() . I want to store the sql server error message thrown while executing proc1() & want to store the error_id & error message in separate log table . error_id is @@Error but how do i trap the sql server error message.
If proc1() raised error saying can not insert NULL value in so & so table. I would requier to store error id & error discription(I do not want to store custom made error discription) in log table.
Following is example
create proocedure proc1()
begin
insert into table1 values select * from table 2 where column id =<value>
end
create procedure usp_driver_proc()
begin
exec proc1()
if @@error <> 0
begin
insert into error_log_table (@@error,<sql server error msg>)
end
end
while executing driver proc proc1() fails & shows following error message
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'column1', table 'ERROR_LOG'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I want to store above error message as i know i will get errorid by @@error
Please let me know how should i store the sql server error msg in log table
If proc1() raised error saying can not insert NULL value in so & so table. I would requier to store error id & error discription(I do not want to store custom made error discription) in log table.
Following is example
create proocedure proc1()
begin
insert into table1 values select * from table 2 where column id =<value>
end
create procedure usp_driver_proc()
begin
exec proc1()
if @@error <> 0
begin
insert into error_log_table (@@error,<sql server error msg>)
end
end
while executing driver proc proc1() fails & shows following error message
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'column1', table 'ERROR_LOG'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I want to store above error message as i know i will get errorid by @@error
Please let me know how should i store the sql server error msg in log table
ASKER
sysmessages stores description in the following format
if error id = 515 then msg =
Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.
means SQLserver generates error message dynamically by putting values at '%.*ls'.
Please suggest me how to do that, its urgent
if error id = 515 then msg =
Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.
means SQLserver generates error message dynamically by putting values at '%.*ls'.
Please suggest me how to do that, its urgent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select @@error,[description] from sysmessages
where error = @@error