Nested Try...Catch

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Nested Try...Catch

Nested Try...Catch

Rate Topic

Display Mode

Topic Options

Author

Message

charles evans

Posted Tuesday, March 11, 2008 1:48 PM

SSC-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

charles evans

Posted Tuesday, March 11, 2008 1:53 PM

SSC-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

Matt Miller (#4)

Posted Tuesday, March 11, 2008 2:04 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 6,998, Visits: 13,950

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

Matt Miller (#4)

Posted Tuesday, March 11, 2008 2:12 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 6,998, Visits: 13,950

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

charles evans

Posted Tuesday, March 11, 2008 3:09 PM

SSC-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

charles evans

Posted Tuesday, March 11, 2008 3:19 PM

SSC-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

Matt Miller (#4)

Posted Tuesday, March 11, 2008 3:25 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 6,998, Visits: 13,950

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

XPSCodes-667952

Posted Tuesday, March 11, 2008 8:55 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 12:45 PM
Points: 83, Visits: 152

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

charles evans

Posted Wednesday, March 12, 2008 11:37 AM

SSC-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 »

Permissions