Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nested Try...Catch Expand / Collapse
Author
Message
Posted Tuesday, March 11, 2008 1:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, 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
Post #467733
Posted Tuesday, March 11, 2008 1:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, Visits: 158
Just checking to make sure I receive any replies to this thread via email.
Post #467739
Posted Tuesday, March 11, 2008 2:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 7,179, Visits: 15,775
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?
Post #467747
Posted Tuesday, March 11, 2008 2:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 7,179, Visits: 15,775
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?
Post #467750
Posted Tuesday, March 11, 2008 3:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, Visits: 158
I get it. I knew it had to be something small that I was overlooking. Thanks!
Post #467782
Posted Tuesday, March 11, 2008 3:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, Visits: 158
Oh and I really didn't know for certain where the error was happening; I was just making an assumption.
Post #467789
Posted Tuesday, March 11, 2008 3:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 7,179, Visits: 15,775
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?
Post #467791
Posted Tuesday, March 11, 2008 8:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 5:24 PM
Points: 83, 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
Post #467876
Posted Wednesday, March 12, 2008 11:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 21, 2011 12:17 PM
Points: 103, 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.
Post #468296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse