Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nested Try...Catch


Nested Try...Catch

Author
Message
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
I so far haven't found any help on the web that would indicate the cause of this. Essentially, the sp I run alway passes an error to the second Catch block in this, even thought the error is encountered in the first Try block. Has anyone had experience with this? Any help would be appreciated. This only happens in this one SP and I have others that use nested try...catch blocks, so I think it must be something I am overlooking.

I am running this on Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) .

Here is the SP:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lsp_ImportDH]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[lsp_ImportDH]
GO

CREATE PROCEDURE [dbo].[lsp_ImportDH]
-- Add the parameters for the stored procedure here
@xml xml = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @idoc int
begin try
begin tran a1
declare @map table
(
HID int not null,
DN int null
primary key (HID)
)
exec sp_xml_preparedocument @idoc OUTPUT, @xml
--populate a temporary table with the data from the xml
insert @map
(
HID,
DN
)
select
HID,
DN
from OPENXML (@idoc, '/COMPANY', 2)
with
(
HID int 'HID',
DN int 'DN'
)
exec sp_xml_removedocument @idoc
insert tblDH
(
DN,
HID,
LastImport
)
select
DN,
HID,
getdate()
from @map m left join tblDH dh
on m.HID = dh.HID
where dh.HID is null
commit tran a1
return 1
end try
begin catch
begin try
begin tran a2
insert LAA.dbo.tblImportHID2DN_Errors
(
ErrorDate,
ErrorNumber,
ErrorSeverity,
ErrorState,
ErrorProcedure,
ErrorLine,
ErrorMessage
)
select
getdate(),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()
commit tran a2
end try
begin catch
rollback tran a2
end catch
exec sp_xml_removedocument @idoc
rollback tran a1
return 0
end catch
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
Just checking to make sure I receive any replies to this thread via email.
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8423 Visits: 18278
My first question would be - how are you sure that the second catch is firing?

According to the way you've got it written, the second TRY/CATCH express is enclosed inside TRANS1, which you roll back (which means trans2 rolls back since it's nested).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8423 Visits: 18278
I wasn't very specific before. You need to move the


rollback tran a1



to be your first statement in the catch. everything prior to that is part of Tran a1, and therefore will be rolled back. That currently includes your error logging TRY...CATCH, meaning - nothing gets logged.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
I get it. I knew it had to be something small that I was overlooking. Thanks!
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
Oh and I really didn't know for certain where the error was happening; I was just making an assumption.
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8423 Visits: 18278
Fair enough. It took me a little staring at it to see an issue and make a guess that that was it.

glad I "got it right"....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
XPSCodes-667952
XPSCodes-667952
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 153
If visual studio is available, you could always use it to debug the SP. My 2 cents.

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot
charles evans
charles evans
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 158
Thanks, I guess I could do a local build of the dev database on my box and set up remote sp debugging on it (we don't want to open things up enough allow the remote debugger on our servers). Thanks for the idea.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search