error log table is rolling back

[fikir]

I have one big proc which is inserting data to more than 15 tables. all the insertion is carried out in a single transaction. The problem is I have an error log table whch catches errors after each insertion.

my question is when the procedure is failing somewhere, I am not seeing any error logged into my error table

is this because when the procedure rolls back, it is also rolling the data inserted in my error table,

what is the best way to keep the errors in the error tables and rolls back the other,

do I have to create a inner transaction each time logging to the error table?

Thanks,

 

[SQLDenis]

is this because when the procedure rolls back, it is also rolling the data inserted in my error table,

of course

use a table variable or variables to store the values and after the rollback insert whatever the values are into the errorlog table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[fikir]

Thank you denis for your suggestion,

I have one que, is is going to executethe insert statement even if the insert statement for the error log if after roll back

i.e

proc

begin tran
.
.
.
@error = @@error
go to error
.
.
.
commit tran


error:
if @error <> 0
roll back


insert into errorlog (desc)
select description
from master..sysmessages
where error = @error

end

is it going to execte the insert to errorlog table

Thanks again

 

[SQLDenis]

after an if you need begin and end if it spans more than 1 line, it is also useful to capture the proc name,step, time, object name (table name) and username for analytics

if @error <> 0
begin
	roll back
	insert into errorlog (desc),'ProcName',getdate(),suser_sname()
	select description
	from master..sysmessages
	where error = @error
end
else
begin
	commit
end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[fikir]

Thanks again Denis

That was a great help

 

[SQLSister]

If it is a Proc with many steps , i prefer to also capture the step it failed on. Just set a variable for the step number at the same time you fill @error, then you will know where exactly the thing failed.

select @error = @@error, @ErrorStep = 1
(repeating changing the step number after each step that you capture an error for)
...
then after the rollback

insert into errorlog (desc,ProcName,errordate,Currentuser, ErrorStep)
select description, 'procname', getdate(), suser_sname(), @ErrorStep
    from master..sysmessages
    where error = @error

"NOTHING is more important in a database than integrity." ESquared