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

Catching the output of RAISERROR() Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 4:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 5:34 AM
Points: 1,043, Visits: 209
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
Posted Thursday, August 16, 2012 5:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, August 16, 2012 5:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 5:34 AM
Points: 1,043, Visits: 209
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
Posted Thursday, August 16, 2012 3:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1346263
Posted Friday, August 17, 2012 12:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223

If you know message_ID and parameters can you quiery sys.messages and reconstruct the actual message?
Post #1346352
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse