May 6, 2008 at 2:40 pm
I have lots of DELETE,INSERT statements.
It's a weekly synchronization update between two databases.
To catch an error I was planning to use
IF @@ERROR <> 0 ....
but then I remembered that I'm in SQL 2005.
Is there any better error handling in SQL 2005?
Something I should consider using?
Rob
May 6, 2008 at 2:50 pm
TRY...CATCH.
eliminiates the need for error check after every statement.
May 6, 2008 at 10:58 pm
Indeed, the new TRY..CATCH construct should be used. The main reason for me would also be not to type the @@ERROR check after each command, but it has more advantages, like automatic transaction rollback. All context information needed is available through a series of functions like ERROR_NUMBER(), ERROR_MESSAGE() etc. etc.
There is only one drawback: keep in mind that catching and handling your exception will 'clear' the exception: unless rethrown by the RAISERROR method the client application will not notice something failed (except for an unexpected result set). It is the way things should work, but since we are not used to it in T-SQL, it has produced a lot of vague bugs already.
[font="Courier New"]
------------------------
MCPD Web Developer, MCPD Windows Developer[/font]
Computers are made to solve problems you did not have in the days they didn't exist.
May 7, 2008 at 6:08 am
Here's a pretty basic intro to Try/Catch[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2008 at 11:49 am
I used this schema:
BEGIN TRY
/*1*/DELETE FROM client
/*2*/INSERT INTO client
....
subsequent DELETE/INSERTS
END TRY
BEGIN CATCH
ROLLBACK TRAN
print 'ROLLBACK TRAN'
EXEC master..MTI_ErrorDetails
END CATCH;
To test Rollback I created a problem with the the second INSERT INTO client.
Error occurred but subsequent DELETE/INSERTS were executed.
Question.
When error occurs in a transaction does it automatically
stops processing right there and gets redirected to TRY CATCH.... ?
Or should I use SET XACT_ABORT ON for that?
May 7, 2008 at 1:04 pm
One thing I recommend is create a proc that handles the logic of your generic error handling. If you find that every catch block you write has "select error_message() as Msg, error_severity() as Severity", or whatever, write that into a proc, and call that proc in your catch block.
Another thing to keep in mind in Try/Catch, is that variables that have been set aren't "unset" by this.
For example, if you have:
Begin Try
insert into dbo.Table (Col1, Col2)
select Val1, Val2
set @ID = scope_identity()
update dbo.Table2
set ColA = Val1
where ID = @ID
if @@rowcount = 0
raiserror('Failed to update Table2', 16, 1)
... more code ...
End Try
Begin Catch
Rollback
exec dbo.Error_CatchCommon
End Catch
Assuming @ID is an output parameter in that proc, the error for failing to update Table2, will not "unset" @ID, and the proc will still return the value to the calling code/procedure. Because of the Rollback command in the Catch block, the data inserted into Table1 will be rolled back. Because of the way Identity columns work, the value for @ID will end up not being in the table, but will show up in the calling code. (This came up with one of the developers here, and boy did it generate a bunch of confusion for them.) So, if you have output parameters, or return values, make sure to manually "unset" them, or otherwise handle them, in your Catch block.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 1:12 pm
GSquared,
But I do that? I call error handling stored procedure?
Don't I?
BEGIN CATCH
ROLLBACK TRAN
print 'ROLLBACK TRAN'
EXEC master..MTI_ErrorDetails
END CATCH;
May 7, 2008 at 1:15 pm
Yeah, I saw that part of your Catch, but wasn't sure if it was what I was suggesting or not. If it is, wonderful, we had the same thought on the thing. I just wasn't sure from what you wrote.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 1:23 pm
user defined procs in master. ewww!
May 7, 2008 at 1:49 pm
antonio.collins (5/7/2008)
user defined procs in master. ewww!
Yeah, that's why I create a Common database for that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 2:12 pm
User defined proc in master
so that you can call it from any database.
In this case I don't have to create the same error_handing proc in each database.
Makes sense?
May 7, 2008 at 2:24 pm
placing the proc in a common database and qualifying the db name (exec common..proc) would achieve the same result without the drawbacks of having the proc in master.
May 7, 2008 at 2:28 pm
riga1966 (5/7/2008)
User defined proc in masterso that you can call it from any database.
In this case I don't have to create the same error_handing proc in each database.
Makes sense?
That works.
Personally, I create a database called "Common", and put that stuff in there. In SQL 2000, I call it with a 3-part name, in 2005, I create a synonym for it and call it that way.
That way, with multiple servers, I can have a copy of Common on each one, and use backup and restore-with-overwrite to keep them all matched up. That way, if I build a new table in it, or modify a table, function, etc., I can easily make sure it matches on all servers.
Can't do that quite as easily if you keep them in Master. Not a big deal, but I find it works better this way for me.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 2:32 pm
We don't have "Common" database.
Should I create one to store common objects?
May 8, 2008 at 5:31 am
GSquared (5/7/2008)
antonio.collins (5/7/2008)
user defined procs in master. ewww!Yeah, that's why I create a Common database for that kind of thing.
Same here. Don't put this stuff in master. Don't put anything in master or msdb. Don't put anything in model that you're not prepared to lose (but you can put stuff there, it's what it's for).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply