error handling

Last Post 18 Sep 2008 04:11 AM by AlexB_SQL. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
AlexB_SQL
New Member
New Member

--
17 Sep 2008 10:24 AM
Hi,

I am trying to trap some T-sql errors and I do not know why it is not working.
Using following script, @msgdescr variable allways returns NULL.
If anyone knows why, please let me know.



create table dba_x
(n1 char(1))
go
insert into dba_x values('a')
go
CREATE TABLE dba_log_upd_coper
(
Execucao datetime,
Erro int,
MsgErro nvarchar(510)
)
go

begin
declare @msgdescr nvarchar(255)
update dba_x set n1='cc' where n1='c'

if @@error <> 0
begin
set @msgdescr=(select description from master..sysmessages where error = @@error)
print 'x' + isnull(@msgdescr,'2')
insert into dba_log_upd_coper (execucao,erro,msgerro)
values (getdate(),@@error,@msgdescr)

end
else
begin
insert into dba_log_upd_coper (execucao,erro,msgerro)
values (getdate(),@@error,'OK')
end
end
nosepicker
New Member
New Member

--
17 Sep 2008 10:53 AM
The @@error function output changes with every SQL statement that is run. Therefore, if you want to retain the value of the @error output from the UPDATE statement, you should save it to a variable. Also, if you're using SQL 2005, your query to sysmessages should specify what language id you want to use (1033 for English):


create table dba_x
(n1 char(1))
go
insert into dba_x values('a')
go
CREATE TABLE dba_log_upd_coper
(
Execucao datetime,
Erro int,
MsgErro nvarchar(510)
)
go

begin
declare @msgdescr nvarchar(255)
DECLARE @error int

update dba_x set n1='cc' where n1='c'
SET @error = @@ERROR

if @error <> 0
begin
set @msgdescr=(select description from master..sysmessages where error = @error AND msglangid = 1033)
print 'x' + isnull(@msgdescr,'2')
insert into dba_log_upd_coper (execucao,erro,msgerro)
values (getdate(),@error,@msgdescr)
end
else
begin
insert into dba_log_upd_coper (execucao,erro,msgerro)
values (getdate(),@error,'OK')
end
end
AlexB_SQL
New Member
New Member

--
18 Sep 2008 04:11 AM
It workeed.
Thanks
You are not authorized to post a reply.

Acceptable Use Policy