Alternative to TRY Catch

Question

Alternative to TRY Catch

Laura_SqlNovice

SSCrazy

Points: 2751
More actions
February 7, 2012 at 12:52 pm

#100440

Hi Guys,
I created a wrapper SP that will execute two SPs...Since this will be in SQL 2000, I wanted to log the errors into table and couldn't use Try Catch....
The Proc should fail and log into the ERROR_LOG...
For testing purposes, I mis-spelt one of the procs executed by the sp_test_error... I am doing something wrong as the Proc is erroring out, but not logging error into ERROR_LOG using sql in PROBLEM:
What I need to do is basically log errors into error table.... If it is possible, I would like to execute the proc that does not error out....
Thanks in advance.
Laura
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_test_error]
AS
BEGIN
DECLARE @intErrorCode INT
DECLARE @source varchar(50)
BEGIN TRAN
SET @source = 'sp_someProc1'
EXEC dbo.sp_someProc1
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0)GOTO PROBLEM
SET @source = 'sp_someProc2'
EXEC dbo.sp_someProc2
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN
Return 0
PROBLEM:
Insert into ERROR_LOG (ERROR_DATE, ERROR_USER, [ERROR_MESSAGE], ERROR_SOURCE, [ERROR_NUMBER], ERROR_HOST)
Select getdate(), SYSTEM_USER, '',@source, @intErrorCode, HOST_NAME()
ROLLBACK TRAN
Return 1
END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

Hi Laura,

We appreciate your post, but this really should not be in the SQL Server 2008 area. Hopefully a moderator can move this to the proper area.

Jared
CE - Microsoft

Laura_SqlNovice SSCrazy Points: 2751 More actions · Answer 2

Thanks KnowItAll I thought about creating in other location, but no one tend to respond in other locations.. I guess less traffic... sorry!

Jared SSC Guru Points: 61793 More actions · Answer 3

No worries, it just gets a bit in the way for those focusing on 2008. You are also more likely to get the proper help in a 2000 forum since some (I don't know what %age) have never used 2000.

Jared
CE - Microsoft

Lowell SSC Guru Points: 323504 More actions · Answer 4

Laura as I remember it, it mostly depends on the error level that gets raised.

anything 16 or above, like foreign key violations, constraint violations, etc cannot be handled in SQL 2000;

if the proc you are using raises an error on it's own via raiserror, I think i have examples of that where you can handle them with IF statements.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Laura_SqlNovice SSCrazy Points: 2751 More actions · Answer 5

Thanks Lowell so much I would like to log error into the table... either I can add that in each Procs or this wrapper proc... I would rather do it in this wrapper proc as this process is back-end job that will be run in off-hour. Thanks again..

Laura

Sergiy SSC Guru Points: 110206 More actions · Answer 6

Error is in the sequence of operations:

PROBLEM:

Insert into ERROR_LOG (ERROR_DATE, ERROR_USER, [ERROR_MESSAGE], ERROR_SOURCE, [ERROR_NUMBER], ERROR_HOST)

Select getdate(), SYSTEM_USER, '',@source, @intErrorCode, HOST_NAME()

ROLLBACK TRAN

ROLLBACK TRAN will roll back everything what's done withing the transaction, including adding a record to ERROR_LOG.

You need to do ROLLBACK first, and then add a record to the ERROR_LOG.

_____________
Code for TallyGenerator