March 23, 2009 at 8:18 am
I have a SPROC that I thought I set up all nice with a transaction and a BEGIN TRY and CATCH but it doesn't fail gracefully. See the following example
CREATE PROC USP_TEST_SPROC
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION TRANNY
SELECT none FROM table_that_does_not_exist
COMMIT TRANSACTION TRANNY
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
ROLLBACK TRANSACTION TRANNY;
END CATCH
END
GO
EXEC USP_TEST_SPROC
GO
DROP PROC USP_TEST_SPROC
GO
This generates the following error for me
Msg 208, Level 16, State 1, Procedure USP_TEST_SPROC, Line 7
Invalid object name 'table_that_does_not_exist'.
Msg 266, Level 16, State 2, Procedure USP_TEST_SPROC, Line 7
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
Is there something I don't understand about Transactions? I expected the first error message, but not the second. Also, note the first error message isn't displayed the way it was supposed to, as if it decided to skip the CATCH
---
Dlongnecker
March 23, 2009 at 8:22 am
First, Try...Catch won't handle missing objects correctly. That level of error skips the Catch block and just crashes and burns. Anything that won't let the code compile (like a missing table or column) works that way.
Second, you don't use transactions around Select. Transactions are for update, delete, or insert. Not that it will break things, but there's just no need for them.
Does that help?
- 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
March 23, 2009 at 8:30 am
Yes that does help.
Is there a reference anywhere as to what errors a CATCH traps exactly? I was under the impression it was almost if not all errors.
Also, if you run this code many times you'll notice the second error starts backing up. After running the code 6 times for example, the error becomes
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 6, current count = 7.
(note the counts)
Then my DB starts filling up with uncommitted transactions that need to be rolled back. Any idea how to fix this?
---
Dlongnecker
March 23, 2009 at 8:33 am
What version of SQL Server are you using? I had this problem when I did not have the right service pack. Upgrading the service pack stops "Pre executing" the transactions within a procedure.
Well, that might help :
SELECT @@Version
I got version 9.0.3042, and when I had something in the 20ish, i had the error.
Cheers,
J-F
March 23, 2009 at 8:34 am
Ok, This is the actual problem I think I am having with my code. I've seen other people write it this way before, so I can't understand why it's wrong/
CREATE PROC USP_TEST_SPROC
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION TRANNY
RAISERROR('lol this sproc is stoopid', 16, 1) // I need to error for some reason, so I do it here
COMMIT TRANSACTION TRANNY
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
ROLLBACK TRANSACTION TRANNY;
END CATCH
END
GO
EXEC USP_TEST_SPROC
GO
DROP PROC USP_TEST_SPROC
GO
And the error:
lol this sproc is stoopid
16
1
Msg 266, Level 16, State 2, Procedure USP_TEST_SPROC, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 7, current count = 0.
---
Dlongnecker
March 23, 2009 at 8:35 am
@@version=Microsoft SQL Server 2005 - 9.00.4211.00 (Intel X86) Jan 30 2009 13:43:44 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
---
Dlongnecker
March 23, 2009 at 8:37 am
It looks like you probably had transactions open from the prior version of the thing. The current count seems to indicate that the change fixed it.
Try closing the connection and running the proc from a new connection.
- 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
March 23, 2009 at 8:41 am
Word!
Thanks. I didn't think about that before. That actually explains the behavior in my actual code.
But I'm still curious if you know off hand any documentation that states exactly what TRY... CATCH actually works on and what it doesn't.
---
Dlongnecker
March 23, 2009 at 9:13 am
Per Books Online:
A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.
Note that it says, "execution errors". Those are what it catches. Compilation errors are what it won't catch. Those are going to be missing objects (tables, views, procs, functions, variables, parameters), misspelled commands, and so on.
Basically, anything that makes a command completely impossible to run, no matter what input parameters you feed it, will generally result in a compilation error. Anything that could run, except the data is bad in some way, will get to the Catch block.
Try a few things like violating a check constraint, inserting into an identity column (without using "set identity_insert on"), inserting duplicate data into a unique index or primary key, deleting data that isn't there, assigning a value to a variable that hasn't been declared, deleting from a view that's not updatable, giving an invalid value to a string function (negative start position or string length), dividing by zero, asking for the square root of a negative number, and so on. See which ones go to Catch and which don't, and you'll get a really good feel for it pretty darn fast.
- 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
March 23, 2009 at 9:25 am
GSquared (3/23/2009)
Per Books Online:A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.
Note that it says, "execution errors". Those are what it catches. Compilation errors are what it won't catch. Those are going to be missing objects (tables, views, procs, functions, variables, parameters), misspelled commands, and so on.
Basically, anything that makes a command completely impossible to run, no matter what input parameters you feed it, will generally result in a compilation error. Anything that could run, except the data is bad in some way, will get to the Catch block.
Try a few things like violating a check constraint, inserting into an identity column (without using "set identity_insert on"), inserting duplicate data into a unique index or primary key, deleting data that isn't there, assigning a value to a variable that hasn't been declared, deleting from a view that's not updatable, giving an invalid value to a string function (negative start position or string length), dividing by zero, asking for the square root of a negative number, and so on. See which ones go to Catch and which don't, and you'll get a really good feel for it pretty darn fast.
It is also really useful to catch an error from dynamic SQL, since the procedure compiles, but gives an error, even for an unknown table :
EXEC sp_executeSQL N'Select * from UnknownTable'
Add this to a try block, and you will get the error, even though the table does not exist at compilation.
Cheers,
J-F
March 23, 2009 at 9:27 am
Right. Because that's an execution problem in the Try block of the calling query. Try adding a Try...Catch to the dynamic SQL command, and see what that does.
- 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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply