Catching the output of RAISERROR()

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Catching the output of RAISERROR()

Catching the output of RAISERROR()

Rate Topic

Display Mode

Topic Options

Author

Message

santhosh_ms3

Posted Thursday, August 16, 2012 4:47 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 06, 2013 12:39 AM
Points: 1,032, Visits: 156

Hi,

Is there anyway to capture the output of raiserror() ?

Error Message is having placeholders %s %s .. so i need to capture the actuall error message which is thrown by raiseerror()..

Regards,
Santhosh.

Post #1345818

ertemen

Posted Thursday, August 16, 2012 5:16 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 8:32 AM
Points: 23, Visits: 236

something like this should work...

declare
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200)

begin try
begin Tran
.....
commit tran
end try
begin catch
begin try
ROLLBACK TRANSACTION
end try
begin catch
end catch

SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();

RAISERROR
(
@ErrorMessage,
16,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);

end catch

Post #1345849

santhosh_ms3

Posted Thursday, August 16, 2012 5:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 06, 2013 12:39 AM
Points: 1,032, Visits: 156

Sorry. Let me reframe my question.

I know the message_id and the passing parameters value for raiserror().

e.g..

raiserror(60071, 10, -1, @A, @B)

Error text for 60071 is "Product %S is not available in %S"

so raisederror would be "Product @A is not available in @B". I need to capture this text.

Post #1345859

opc.three

Posted Thursday, August 16, 2012 3:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 6,711, Visits: 11,743

Catch it from where? In T-SQL or an app language like C#?

If your supplying a severity level of 10 then it will not trip a T-SQL CATCH block. Your RAISERROR with severity 10 is equal to a PRINT statement.

If connecting to SQL Server from .NET you can capture everything coming from the INFO message stream which is where PRINT and warning (i.e. RAISERROR with severity 10 or less) messages are written. If you're using .NET look into the SqlInfoMessageEventHandler class.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1346263

rVadim

Posted Friday, August 17, 2012 12:47 AM

SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 19, 2013 1:36 PM
Points: 887, Visits: 2,062

If you know message_ID and parameters can you quiery sys.messages and reconstruct the actual message?

Post #1346352

« Prev Topic | Next Topic »

Permissions