ERROR_MESSAGE()/ERROR_NUMBER()

• graham.haller

  SSC Enthusiast

  Points: 158

  More actions

  October 25, 2007 at 3:33 am

  #180611

  I have a stored procedure that removed and recreates constraints and indexes on a database. I am using try catch blocks to capture any errors to a log and then

  reporting these at the end of the execution.

  The problem I have found is that ERROR_MESSAGE() and ERROR_NUMBER() only return the last error encountered, so that the message in my error log

  only reads Could not create constraint. See previous errors. Not very helpful!

  Running the failed command on its own would generate a error message

  Msg 2714, Level 16, State 4, Line 1

  There is already an object named 'my_constraint_name' in the database.

  Msg 1750, Level 16, State 0, Line 1

  Could not create constraint. See previous errors.

  Now the first message returned is helpful, it tells me why the command has failed, and not just the fact it has failed.

  What I am struggling to do is find a way to get that first meaningful message into my error log rather than the less useful second message, which seems to be the default. Is there a way of doing it ?

• matthew.rhea

  SSC Rookie

  Points: 44

  More actions

  September 14, 2009 at 9:24 am

  #1053285

  Did you ever figure out how to trap the first error? I need to do that, too. Thanks.

• JustASQLGuy

  SSC-Addicted

  Points: 447

  More actions

  October 9, 2009 at 10:26 am

  #1064229

  Consider using code that catches the error and raises the error to the calling program:

  proc1:

  begin try

  exec proc2;

  end try

  begin catch

  declare

  @errMsg varchar(1000),

  @errState int,

  @errSeverity int;

  set @errState = error_state();

  set @errSeverity = error_severity();

  set @errMsg = error_message() + char(10) + error_procedure();

  raiserror( @errMsg, @errSeverity, @errState );

  end catch;

  proc2:

  begin try

  <do stuff>

  end try

  begin catch

  declare

  @errMsg varchar(1000),

  @errState int,

  @errSeverity int;

  set @errState = error_state();

  set @errSeverity = error_severity();

  set @errMsg = error_message() + char(10) + error_procedure();

  raiserror( @errMsg, @errSeverity, @errState );

  end catch;