April 7, 2008 at 11:55 pm
Hi, when doing my error handling in sql 2000 I managed to retrieve what block of code the error occurred in. This was very useful for either custom actions and / or custom error text in the output or logging tables.
e.g I had a long stored procedure and would break it into parts.
if an error occured in part 4 then this would be caught, and the step description put in a variable, the focus passed to the err block and I would know exactly where the error occured which would save time troubleshooting because I could go straight to the block of code and the text was descriptive in the err logging table. Especially when the same code might be duplicated in the SP.
simple mock up of this below.
Although in SQL 2005 error handling is easier and cleaner to set up with more information being returned -- does anybody know if it is possible to replicate the operation from SQL 2000?
Raiserror would not work because the failure would happen before this is executed.
The line number is returned,sure -- but in a logging table line 533 of procedure 'xyz' does not mean as much as:
Error Number:Msg 8134, Level 16, State 1, Line 22 Divide by zero error encountered.
Code Block:'Step number 2 Divide two numbers caused an error'
Thanks
---------------------------------------------
set nocount on
declare @error int
declare @step varchar(50)
if (object_id('tempdb..#x') is not null) drop table #x
create table #x(col1 tinyint)
-- part 1
insert into #x(col1)values(12)
set @error = @@error
if @error <> 0
begin
set @step = 'Step number 1'
goto err
end
-- part 2
select 1/0
set @error = @@error
if @error <> 0
begin
set @step = 'Step number 2 Divide two numbers generated an error'
goto err
end
return
err:
print 'In Error Block'
print 'This is the step number that can be logged for easy easier troubleshooting --> The error was in
code block 2' + @Step
print @error
--------------------------------------------------------------------
April 8, 2008 at 12:33 am
cannot see any reason why you couldn't do this same thing with TRY..CATCH blocks.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2008 at 1:06 am
I must be missing something then because in SQL 2000 the line after the error gets processed hence the @@error can be captured and acted on , yet in SQL 2005 it looks as though processing stops at the line of the error and processing recommences at the CATCH block.
I could populate the step before the potentially offending code but that seems a bit silly...thanks
April 8, 2008 at 2:30 am
it might be that Iām only on my first cup of coffee, but i fail to see what you intend to gain by adding the GOTO lines, except only writing print once.
in your example you are inserting into a temp table, then i will assume that you want to select something from that table or ?
Now if I was constructing this in TSQL 8 with my only error handling as @@error here how it would sort of look like.
create table #x(col1 int , col2 int)
go
--exec RunAndReport 1,2
create procedure RunAndReport @col1 int , @col2 int
as
begin
declare @err int
insert into #x(col1,col2)values(@col1,@col2)
set @err = @@error
if(@err=0)
begin
select 1/0
set @err = @@error
if(@err>0) begin
print @err
end
end
else begin
print 'Error inserting into tmp table ' + @err
end
end
This will give us the errors of all errors š
But it gives you the correct error line , line 9 in the procedure.
Msg 8134, Level 16, State 1, Procedure RunAndReport, Line 9
Divide by zero error encountered.
8134
kgunnarsson
Mcitp Database Developer.
April 8, 2008 at 3:33 am
Hi, the above 2000 example concept works well for the 2000 procs. The production procedures might be say 1000 lines and it is broken into logical parts. When an error occurs I can easily log not only the error number but also the block of code that the error happened in. This will be in plain english (as I wrote it not MS) and can be logged as well so I can go straight to the specific query in the procedure.
I don't know how to replicate this in 2005 using the try..catch scenario. I can get details of course and the line number but don't seem to have the ability to add any custom details like in the 2000 example.
Apart from this the try..catch works great.
April 8, 2008 at 10:44 pm
mark (4/8/2008)
I must be missing something then because in SQL 2000 the line after the error gets processed hence the @@error can be captured and acted on , yet in SQL 2005 it looks as though processing stops at the line of the error and processing recommences at the CATCH block.
It does, you just capture the error info in the CATCH block and act on it there. Make the TRY..CATCH blocks smaller and they are effectively each separately encapsulating the same code that you are after-testing with @@error now.
I could populate the step before the potentially offending code but that seems a bit silly...thanks
That's what I do. I can't see that it's materially any different from populating it afterwards.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 9, 2008 at 3:30 am
I think that is were I was wrong. I was only using one try..catch block to encompass the entire code.
If you mean use smaller blocks then yes I see this does work and if required enables me to get specific information about each code block if required (like the @@error), or just have the error go to the outer catch block if I am not worried about it as much.
thanks
begin try
-- code block 1
begin try
select 1/0
end try
begin catch
raiserror('errror in code block 1',16,1)
end catch
end try
begin catch
select error_message()
end catch
April 9, 2008 at 6:26 am
Yep, that's it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply