Capturing Error messages

• rs

  SSCrazy

  Points: 2819

  More actions

  May 10, 2004 at 1:07 pm

  #111228

  Hi,

  If execute the following code

  USE tempdb

  GO

  CREATE TABLE #TEMP (ID [INT] NOT NULL)

  DECLARE @x INT

  INSERT INTO #TEMP(ID) VALUES (@x)

  DROP TABLE #TEMP

  i get the following error

  Server: Msg 515, Level 16, State 2, Line 6

  Cannot insert the value NULL into column 'ID', table 'tempdb.dbo.#TEMP_______________________________________________________________________________________________________________000000000023'; column does not allow nulls. INSERT fails.

  The statement has been terminated.

  Is there any way to capture this error message with in a stored procedure (if i get these type of error messages in stored procedures, i want store error messages into a table). Becuase if look into sysmessages table for error 515

  SELECT     *

  FROM         sysmessages

  WHERE     (error = 515)

  Cannot insert the value NULL into column '%.*ls', table '%.*ls'; column does not allow nulls. %ls fails.

  If you have any ideas please help me.

  Thanks.

• gaetano.lombardo

  Mr or Mrs. 500

  Points: 549

  More actions

  May 11, 2004 at 2:16 am

  #505920

  May be you can use the sp_altermessage 515, 'WITH_LOG', 'true' to write to log!

  Although I remember a very nice article by Amit Jethva just some day ago... take the time to read it, I think is just what you want!

  http://www.sqlservercentral.com/columnists/ajethva/capturingtheerrordescriptioninastoredprocedure.asp

  Ciao,

  Marco.

  
  "...e il mio maestro mi insegnò com'è difficile trovare l'alba dentro l'imbrunire" F.Battiato - Prospettiva Nevsky

• Alamir

  SSCertifiable

  Points: 6085

  More actions

  May 12, 2004 at 2:53 am

  #506091

  I think you have to check @@error global variable after any inser, update or delete ..

  and

  if (@@error 0) /* then error happened */

  insert @error into myErrorTable (you made)

  I hope this help u

  
  Alamir Mohamed
  Alamir_mohamed@yahoo.com