Try Catch Transaction Scenario

Question

Post reply

Try Catch Transaction Scenario

toddasd

SSCertifiable

Points: 6298
More actions
May 23, 2011 at 3:41 pm

#240969

Consider the following procedure:
ALTER PROCEDURE [dbo].[sp_VM_BW_VesselSwap]
@JobsID1 INT,
@JobsID2 INT = NULL,
@VesselID INT = NULL,
@ServiceStop DATETIME
AS
--RAISERROR('Job swap failed.', 16,1)
Insert into TestLogError values('first', Getdate())
BEGIN TRY
BEGIN TRANSACTION
SELECT 1/0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
Insert into TestLogError values('second', Getdate())
RAISERROR('Job swap failed.', 16,1)
END CATCH
When executing the procedure as is listed above from the app (in Access), the front-end displays no error message. But clearly it is catching the divide error because I'm getting two rows in TestLogError.
When the first RAISERROR is uncommented and the procedure applied, the front-end displays the error message.
Can anyone offer some insight as to the difference in the position of the RAISERROR statements? My objective is to get the the second RAISERROR to behave like the first and display the error.
TIA
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Have you tried setting NOCOUNT ON in the proc?

I have seen instances of client code using ADO that does not behave as expected due to the presence of row counts (from the INSERTs).

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

toddasd SSCertifiable Points: 6298 More actions · Answer 2

I actually did think about that 20 minutes after I posted and tried that change, but to no affect.

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

toddasd SSCertifiable Points: 6298 More actions · Answer 3

A friend (sql guru in his own right) helped me figure it out. In my example above, the SELECT 1/0 is returning a result before the error is thrown. It shows in SSMS as both Results Tab and Messages tab. Throwing the error first only creates a Message. To fix it here, you can assign the result so it's not selected, as in SELECT @X = 1/0

In my actual proc that I'm trying to add this error handing has UPDATES and INSERTS in the transaction, so mister.magoo is inadvertently correct, I need a SET NOCOUNT ON. 😉

(Shout out to M.L., you are the man :cool:)

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.