[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

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
0
swapnilsardeshpande
Asked:
swapnilsardeshpande
1 Solution
 
stevetheskiCommented:
insert into my errortable (errornum, [error])
select @@error,[description] from sysmessages
where error = @@error
0
 
swapnilsardeshpandeAuthor Commented:
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

0
 
Melih SARICAIT ManagerCommented:

u can create an alert to collect these kind of information ..

check sp_add_jobstep  from bol..

here is an example of some params usage


if [A-ERR] <> -1
begin
DECLARE @msg nvarchar(4000)
declare @JobName varchar(200)

Set @msg = REPLACE("Error: [A-ERR]  <br>
Severity: [A-SEV] <br>
Date: [STRTDT]  <br>
Time: [STRTTM]  <br>
Database: [A-DBN]  <br>
Message: [A-MSG] ", "'", "")


set  @JobName = 'Error no : [A-ERR] '
 insert into table what ever u wannt
end
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now