Link to home
Start Free TrialLog in
Avatar of swapnilsardeshpande
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
Avatar of stevetheski
stevetheski
Flag of United States of America image

insert into my errortable (errornum, [error])
select @@error,[description] from sysmessages
where error = @@error
Avatar of swapnilsardeshpande
swapnilsardeshpande

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

ASKER CERTIFIED SOLUTION
Avatar of Melih SARICA
Melih SARICA
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial