Catching the output of RAISERROR()

• santhosh_ms3

  Default port

  Points: 1482

  More actions

  August 16, 2012 at 4:47 am

  #265106

  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.

• ertemen

  SSC-Addicted

  Points: 479

  More actions

  August 16, 2012 at 5:16 am

  #1526486

  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

• santhosh_ms3

  Default port

  Points: 1482

  More actions

  August 16, 2012 at 5:30 am

  #1526494

  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-2)

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

  so raisederror would be "Product @a is not available in @b-2". I need to capture this text.

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  August 16, 2012 at 3:14 pm

  #1526847

  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

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 17, 2012 at 12:47 am

  #1526918

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

  --Vadim R.