July 28, 2009 at 4:46 am
i am doing the next try catch raiseerror :
DECLARE @Err int
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SET @Err = 0
BEGIN TRY
INSERT vw_Billing....
VALUES.....
END TRY
BEGIN CATCH
SET @Err = @@ERROR
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
END CATCH
IF @Err = 2627 RETURN 0
RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState );
the thing is that withogut the try catch i see the error like this :
Msg 4457, Level 16, State 1, Procedure casp_InsertLaRbLevel1, Line 38
The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.
but if i do this with RAISEERROR waht i see is not an error but an "information" error :
Msg 50000, Level 16, State 1, Procedure casp_InsertLaRbLevel1, Line 64
The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions.
you can see that the Msg values has changed to 50000 instead of 4457.
this problem causes the service that writes to the DB to see this Error in the level of "Information" and not as "Error"
how can i fix it?
Thanks
Peleg
July 28, 2009 at 4:55 am
peleg k (7/28/2009)
BEGIN CATCHSET @Err = @@ERROR
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
END CATCH
IF @Err = 2627 RETURN 0
RAISERROR (@ErrorMessage,
@ErrorSeverity,
@ErrorState );
Your set went OK, so there is nomore info for your select !
change it to
SElecT @Err = @@ERROR
, @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 28, 2009 at 7:38 am
it still dosent help
seems that only add_message can help in this case
July 28, 2009 at 11:35 am
It's been a while since I set up my stored procedure template with error handling, but I *think* you need to use the ERROR_NUMBER() function inside the CATCH block. But, since you are not providing the errr_number to the raiserror function it will produce an generic error level of 50000, which is what you are seeing.
Per BOL:
When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.
July 28, 2009 at 12:02 pm
Some samples (I'm not sure where I got them from) :
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
CREATE PROCEDURE spc_ErrorDetails
AS
Begin
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END
go
--This procedure can be executed in your CATCH block.
--STEP II: Your actual code where all the processing is done.
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0 AS MyDivideByZero;
END TRY
BEGIN CATCH
EXEC spc_ErrorDetails
END CATCH;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 28, 2009 at 3:38 pm
Lamprey13 (7/28/2009)
It's been a while since I set up my stored procedure template with error handling, but I *think* you need to use the ERROR_NUMBER() function inside the CATCH block. But, since you are not providing the errr_number to the raiserror function it will produce an generic error level of 50000, which is what you are seeing.Per BOL:
When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.
The thing is that you can only use a msg_id that is >=50000
and to do that you need to use sp_addmessage to create your own error
July 28, 2009 at 6:16 pm
I created this function for use in CATCH blocks.
CREATE PROC dbo.RethrowError AS
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = CASE WHEN ERROR_STATE() > 0 THEN ERROR_STATE() ELSE 1 END,
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'),
@ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,
@ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine );
GO
GRANT EXECUTE ON dbo.RethrowError TO public
It will generate a 50000 error, there's no workaround for that, but the error message will correctly show the original error number.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply